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

Query below lists tables that are not referenced by the foreign keys.

See also:

Query

select 'No FKs >-' foreign_keys,
    tab.tabschema as schema_name,
    tab.tabname as table_name
from syscat.tables tab
left outer join syscat.references ref
    on ref.reftabschema = tab.tabschema  and ref.reftabname = tab.tabname
where
    tab.type = 'T'
    and tab.tabschema not like 'SYS%'
    and ref.constname is null     
order by tab.tabschema, tab.tabname

Columns

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

Rows

  • One row represents one table
  • Scope of rows: all tables in a database that are not referenced by the foreign keys
  • Ordered by schema, table name

Sample results

List of tables in AdventureWorks not referenced by foreign keys:

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