Find tables without relationships - Loner Tables - in SQL Server database

Piotr Kononow - Dataedo Team Piotr Kononow 2018-06-25

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,
        fks.tab as [table],
        '>- no FKs' fks
     from
        (select schema_name(tab.schema_id) + '.' + tab.name as tab,
            count(fk.name) as fk_cnt
        from sys.tables as tab
            left join sys.foreign_keys as fk
                on tab.object_id = fk.parent_object_id
        group by schema_name(tab.schema_id), tab.name) fks
        inner join 
        (select schema_name(tab.schema_id) + '.' + tab.name as tab,
            count(fk.name) ref_cnt
        from sys.tables as tab
            left join sys.foreign_keys as fk
                on tab.object_id = fk.referenced_object_id
        group by schema_name(tab.schema_id), tab.name) refs
        on fks.tab = refs.tab
    where fks.fk_cnt + refs.ref_cnt = 0
    

    Columns

    • refs - icon symbolizing lack of references by foregin key constraints
    • 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

    List of Loner Tables in AdventureWorks:

    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