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.


SELECT  tab.DatabaseName,
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',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
AND tab.TableKind = 'T'
ORDER BY    tab.DatabaseName,


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


  • 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.

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.