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 count(*) as [table_count],
    sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
    as [loner_tables],
    cast(cast(100.0 * sum(case when fks.cnt + refs.cnt = 0 then 1 else 0 end)
    / count(*) as decimal(36, 1)) as varchar) + '%' as [loner_ratio]
from (select schema_name(tab.schema_id) + '.' + tab.name as tab,
        count(fk.name) as 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 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

Columns

• table_count - number of tables in the database
• loner_tables - number of Loner Tables in the database
• loner_ratio - Loner Ratio - % of Loner Tables in the database

Rows

• Scope of rows: the query returns one row

Sample results

Loner Ratio in the AdventureWorksLT database is 33.3%, meaning that 33.3% of tables are not related by foreign keys to any other table.