Find tables without relationships - Loner Tables - in Teradata database

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.