List table columns with their foreign keys in Teradata database

Query allows to find all columns from database with their references if there is any.


SELECT  col.DataBaseName || '.' || col.TableName AS ForeignTable,
        '>-' AS Rel,
        ref.ParentDB || '.' || ref.ParentTable AS PrimaryTable,
        ref.ParentKeyColumn AS PrimaryColumn
FROM DBC.ColumnsV col
LEFT JOIN DBC.All_RI_ChildrenV ref
ON  ref.ChildDB = col.DataBaseName
AND ref.ChildTable = col.TableName
AND ref.ChildKeycolumn = col.ColumnName
WHERE   col.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')
ORDER BY    ForeignTable,


  • ForeignTable - table database name followed by table name
  • ColumnName - column name
  • Rel - relationship symbol ('>-') indicating foreign key and direction
  • PrimaryTable - referenced table name preceded by database name
  • PrimaryColumn - referenced column


  • One row represents one column
  • Scope of rows: all columns in a database
  • Ordered by foreign table database and name, primary table database and name

Sample results

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.