Find tables without relationships - Loner Tables - in Azure SQL Database

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

The query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something that can be called as Loner Tables. This diagram illustrates the concept:

Learn more about Loner Tables

Query

select 'No FKs >-' as refs,
    fks.tab as [table],
    '>- no FKs' as 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) as fks
    inner join
    (select schema_name(tab.schema_id) + '.' + tab.name as tab,
        count(fk.name) as 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) as refs
    on fks.tab = refs.tab
where fks.fk_cnt + refs.ref_cnt = 0

Columns

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

Rows

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

Sample results

List of Loner Tables in the AdventureWorksLT database:

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