Find tables without foreign keys in Db2 database

Query below lists all tables that do not have foreign keys.

See also:

Query

select tab.tabschema as schema_name,
    tab.tabname as table_name,
    '>- no FKs' foreign_keys
from syscat.tables tab
left join syscat.tabconst const 
    on const.tabschema = tab.tabschema 
    and const.tabname = tab.tabname 
    and const.type = 'F'
where 
    tab.type = 'T'
    and tab.tabschema not like 'SYS%'
    and const.constname is null

Columns

  • schema_name - table schema name
  • table_name - table name
  • foreign_keys - symbol indicating lack of FKs

Rows

  • One row represents one table that doesn't have a foreign key
  • Scope of rows: all tables in a database that don't have foreign keys (do not refer to other tables)
  • Ordered by schema and table name

Sample results

List of tables in SAMPLE without foreign keys:

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