5

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?

Community
  • 1
  • 1
Rocketq
  • 5,423
  • 23
  • 75
  • 126
  • 1
    `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 –  Feb 04 '15 at 10:54

2 Answers2

9

You are looking for this:

SELECT tablename
FROM dbc.columnsV
WHERE ColumnName in ('col1', 'col2')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 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
4

This query works with me :

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