Find table that DON'T have a column with specific name in Teradata database

Databases often have standard columns. Examples of such standard columns can be id, modified_date, created_by or row_version.

Query below finds all tables that do not have a 'last_update' column.

Query

SELECT  tab.DatabaseName,
        tab.TableName
FROM    DBC.TablesV tab
LEFT JOIN DBC.ColumnsV col
ON tab.DatabaseName = col.DatabaseName
AND tab.TableName = col.TableName
AND col.ColumnName = 'id' -- provide column name here
WHERE col. ColumnName is null
AND tab.DatabaseName NOT IN ('All', 'Crashdumps', 'DBC', 'dbcmngr', 
    'Default', 'External_AP', 'EXTUSER', 'LockLogShredder', 'PUBLIC',
    'Sys_Calendar', 'SysAdmin', 'SYSBAR', 'SYSJDBC', 'SYSLIB',
    'SystemFe', 'SYSUDTLIB', 'SYSUIF', 'TD_SERVER_DB', 'TDStats',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
AND tab.TableKind = 'T'
ORDER BY    tab.DatabaseName,
            tab.TableName;

Columns

  • DatabaseName - name of database
  • TableName - name of table

Rows

  • One row represents a table
  • Scope of rows: all found tables which don't have column with provided column name
  • Ordered by database name, table name

Sample results

These tables do not have a 'id' column.

0
There are no comments. Click here to write the first comment.