Find tables without relationships - Loner Tables - in Db2 database

Marcin Nagly - Dataedo Team Marcin Nagly 2018-06-30

Table of Contents:


    This query listed tables that have no foreign keys, meaning they are not referencing any table or are not on the "many" side of FK.

    Query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something I called Loner Tables. This diagram illustrates the concept:

    Learn more about Loner Tables

    Query

    select 'No FKs >-' refs,
        tab.tabschema concat '.' concat tab.tabname as table, 
        '>- no FKs' fks
    from 
    syscat.tables tab
    left outer join syscat.tabconst const 
        on const.tabschema = tab.tabschema 
        and const.tabname = tab.tabname and const.type ='F'
    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 const.constname is null
        and ref.constname is null
    

    Columns

    • refs - icon symbolizing lack of references by foregin key
    • table - name of the table with schema name
    • fks - icon symbolizing lack of foregin key constraints

    Rows

    • One row represents one table
    • Scope of rows: tables that are not related (not refererencing and not being referenced with foreign key constraints) with any table
    • Ordered by schema table name

    Sample results

    Loner Tables in GSDB:

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept