Find tables without foreign keys in Teradata database

This query returns tables without foreign keys.

See also:


SELECT  DataBaseName,
        '>- 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',
    'tdwm', 'SQLJ', 'TD_SYSFNLIB', 'SYSSPATIAL')
ORDER BY    DatabaseName,


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


  • 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

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