The query below lists tables with most relationships (both foreign keys and FK references from other tables).

See also:

• Tables with most foreign keys

Query

select tab as [table],
    count(distinct rel_name) as relationships,
    count(distinct fk_name) as foreign_keys,
    count(distinct ref_name) as [references],
    count(distinct rel_object_id) as related_tables,
    count(distinct referenced_object_id) as referenced_tables,
    count(distinct parent_object_id) as referencing_tables
from
    (select schema_name(tab.schema_id) + '.' + tab.name as tab,
        fk.name as rel_name,
        fk.referenced_object_id as rel_object_id,
        fk.name as fk_name,
        fk.referenced_object_id,
        null as ref_name,
        null as parent_object_id
    from sys.tables as tab
        left join sys.foreign_keys as fk
            on tab.object_id = fk.parent_object_id
    union all
    select schema_name(tab.schema_id) + '.' + tab.name as tab,
        fk.name as rel_name,
        fk.parent_object_id as rel_object_id,
        null as fk_name,
        null as referenced_object_id,
        fk.name as ref_name,
        fk.parent_object_id
    from sys.tables as tab
        left join sys.foreign_keys as fk
            on tab.object_id = fk.referenced_object_id) as q
group by tab
order by count(distinct rel_name) desc

Columns

• table - name of table with its schema name
• relationships - number of relationships (FKs and FK references)
• foreign_keys - number of foreign keys in a table
• references - number of FK references from other tables
• related_tables - number of different related tables (regardless of the relationship type/direction, the table is counted only once)
• referenced_tables - number of different tables referenced with FKs (please note that the table can be related more than once, so the number of FKs and number of referenced tables can be different)
• referencing_tables - number of different tables referencing with foreign keys this table

Rows

• One row: represents one table in a database
• Scope of rows: all tables in a database
• Ordered by: number of relationships (foreign keys and references) from the ones with the most

Sample results