List table columns with their foreign keys in Teradata database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-10-31

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept