TERADATA: SEARCH FOR COLUMNNANE IN DATABASE-CodePudding

I have a database name called 'xxx' which returns a bunch of tables.. I want to be able to return only the tables that have a specific column in them. I managed go get all the tables to show:

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

HOW Can I return only the tables that have a specific column.

CodePudding user response：

You can use columnsV table instead:

SELECT  DISTINCT tablesV.DatabaseName,
        tablesV.TableName,
        tablesV.CreateTimeStamp,
        tablesV.LastAlterTimeStamp
FROM    DBC.TablesV 
     INNER JOIN DBC.ColumnsV
         ON tablesV.databasename = columnsV.databasename
         AND tablesV.tablename = columnsV.tablename
WHERE   tablesV.TableKind IN ('T','O') and     tablesV.DatabaseName = 'XXX'
     AND columnsV.ColumnName = 'specialColumnName'
ORDER BY    TableName;

Also I added O to your TableKind so you don't miss out on any NOPI tables in your environment.