Find tables without relationships - Loner Tables - in Teradata database

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

Table of Contents:


    Query below lists something we called Loner Tables. Loner tables are tables not related by FKs with any other table. This diagram illustrates the concept:

    Learn more about Loner Tables

    Query

    SELECT  '>- No FKs' as Refs,
            DatabaseName,
            TableName,
            '>- No FKs' as ForeignKeys
    FROM    DBC.TablesV Tab
    LEFT JOIN   DBC.All_RI_ParentsV Fks
    ON  (Fks.ParentDB = Tab.DatabaseName
        AND Fks.ParentTable = Tab.TableName)
    OR  (Fks.ChildDB = Tab.DatabaseName
        AND Fks.ChildTable = Tab.TableName)
    WHERE   TableKind = 'T'
    AND Fks.ParentTable IS NULL
    AND Tab.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', 'TD_SYSFNLIB',
        'TD_SYSGPL', 'TD_SYSXML', 'TDMaps', 'TDPUSER', 'TDQCD', 'TDStats',
        'tdwm', 'SQLJ', 'SYSSPATIAL')
    ORDER BY    DatabaseName,
                TableName;
    

    Columns

    • Refs - icon symbolizing absence of references by foregin key
    • DatabaseName - name of the database
    • TableName - table name
    • ForeignKeys - icon symbolizing absence of foregin key

    Rows

    • One row: represents one table
    • Scope of rows: tables without FKs and not referenced by FKs
    • Ordered by: database name, table 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 (updated 28-04-2020).
    Accept