List tables with most relationships in SQL Server database

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

See also:


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) + '.' + as tab, as rel_name, fk.referenced_object_id as rel_object_id, 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) + '.' + as tab, as rel_name, fk.parent_object_id as rel_object_id, null as fk_name, null as referenced_object_id, 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) q group by tab order by count(distinct rel_name) desc


  • table - name of table with 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 relationship type/direction table is counted only once)
  • referenced_tables - number of different tables referenced with FKs (please note that table can be related more than once so number of FKs and number of referenced tables can be different)
  • referencing_tables - number of different tables referencing with foreign keys this table


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

Sample results

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.