List foreign keys with columns in Teradata database

This query returns columns defined as foreign keys with corresponding them primary columns.

Query

SELECT  ChildDB || '.' || ChildTable AS ForeignTable,
        '>-' AS Rel,
        ParentDB || '.' || ParentTable AS PrimaryTable,
        ChildKeyColumn AS ForeignColumn,
        ' = ' AS "Join",
        ParentKeyColumn AS PrimaryColumn
FROM    DBC.All_RI_ChildrenV
WHERE   ChildDB 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 - referencing table database followed by name
  • Rel - relationship symbol implicating direction
  • PrimaryTable - referenced table database followed name
  • ForeignColumn - referencing column name
  • join - "=" symbol indicating join operation for pair of columns
  • PrimaryColumn - referenced column name

Rows

  • One row represents one column used in foreign key. Even if foreign key is composite key, each column appears separately.
  • Scope of rows: all columns defined as foreign key
  • Ordered by database name and name of foreign table, database name and name of referenced table

Sample results

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