List foreign keys in Teradata database

Query below returns foreign keys defined in a database.

Query

SELECT  ChildDB || '.' || ChildTable as ForeignTable,
        '>-' as Rel,
        ParentDB || '.' || ParentTable as PrimaryTable,
        TRIM(TRAILING ','
                FROM XMLAGG(ChildKeyColumn)(varchar(1000)))
            AS ForeignColumns
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')
GROUP BY    ForeignTable,
            PrimaryTable
ORDER BY    ForeignTable,
            PrimaryTable;

Columns

  • ForeignTable - foreign table database and name
  • Rel - relationship symbol implicating direction
  • PrimaryTable - primary (rerefenced) table database and name
  • ForeignColumns - list of FK colum names, separated with ","

Rows

  • One row represents one foreign key. If foreign key consists of multiple columns (composite key) it is still represented as one row.
  • Scope of rows: all foregin keys in a database
  • Ordered by foreign database name and table name

Sample results

You could also get this

Get this interactive HTML data dictionary in minutes with Dataedo.

See live HTML data dictionary sample

Try for free

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