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:

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) q group by tab order by count(distinct rel_name) desc

Columns

  • 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

Rows

  • 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

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