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.

Query

SELECT  col.DataBaseName || '.' || col.TableName AS ForeignTable,
        col.ColumnName,
        '>-' 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',
    'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
ORDER BY    ForeignTable,
            PrimaryTable;

Columns

  • 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

Rows

  • 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

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