How to find all the tables in database Teradata with specific column names in them?

Question

I have 2-3 different column names that I want to look up in the entire DB and list out all tables which have those columns. Any easy query?

I have seen solution for MySQL, which won't work here because TD as far as I know don't have schemes, but instead I found this.

And tried this code:

SELECT TableName
FROM DBC.COLUMNS
WHERE DatabaseName = 'DB_NAME' and
ColumnName in ('col1', 'col2')

But surely subquery must be used to get TableName, because DBC.COLUMNS doesn't have that field. Any further ideas?

`DBC.Columns` seems to be what you are looking for: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1092_111A/Views_Reference.015.134.html#ww25068441 — a_horse_with_no_name, Feb 04 '15 at 10:54

Gordon Linoff · Accepted Answer · 2015-02-04T22:50:50.473

9

You are looking for this:

SELECT tablename
FROM dbc.columnsV
WHERE ColumnName in ('col1', 'col2')

edited Feb 04 '15 at 22:50

answered Feb 04 '15 at 12:01

Gordon Linoff

1,198,228
53
572
709

I updated my question, my chief says that sub query must be used – Rocketq Feb 04 '15 at 12:25
2

@Rocketq . . . of course the `tablename` is in the table. Being told to use a subquery is counter-productive. – Gordon Linoff Feb 04 '15 at 12:28
You right, it's in the table, I checked `show select* from DBC.Columns` – Rocketq Feb 04 '15 at 12:37
1

Please change the view to **dbc.ColumnsV**, dbc.Columns is an old legacy version, deprecated since TD12. Starting with TD14.10 it might return wrong results if object names longer than 30 characters are used... – dnoeth Feb 04 '15 at 15:19
@dnoeth . . . Thank you. – Gordon Linoff Feb 04 '15 at 22:50

score 3 · Answer 2 · answered Jan 20 '20 at 20:20

3

This query works with me :

SELECT  DatabaseName,
        TableName,
        CreateTimeStamp,
        LastAlterTimeStamp
FROM    DBC.TablesV
WHERE   TableKind = 'T'
and     DatabaseName = 'YOUR_SCHEMA'
ORDER BY    TableName;

answered Jan 20 '20 at 20:20

Thiago Nunes

31
3

How to find all the tables in database Teradata with specific column names in them?

2 Answers2