Find tables that are not referenced by the foreign keys in Teradata database

This query lists not referenced tables by any foreign key.

See also:

Query

SELECT  '>- No FKs' as ForeignKeys,
        DataBaseName,
        TableName
FROM    DBC.TablesV Tab
LEFT JOIN   DBC.All_RI_ParentsV Fks
ON  Fks.ParentDB = Tab.DatabaseName
AND Fks.ParentTable = 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

  • ForeignKeys - symbol indicating lack of FK references
  • DatabaseName - name of database containing table
  • TableName - table name

Rows

  • One row represents one table
  • Scope of rows: all not referenced tables
  • Ordered by database name, table name

Sample results

Tables not referenced by any FK:

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