Find tables without foreign keys in Teradata database

This query returns tables without foreign keys.

See also:

Query

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

Columns

  • DatabaseName - database name
  • TableName - table name
  • ForeignKeys - symbol indicating lack of FKs

Rows

  • One row represents one table with no foreign key
  • Scope of rows: tables in a database that haven't foreign keys (do not refer to other tables)
  • Ordered by database name and table name

Sample results

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