List tables with most foreign keys in SQL Server database

Query below lists tables with their number of foreign keys and number of tables they refer to.

See also:

Query

select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as [table],
    count(*) foreign_keys,
    count (distinct referenced_object_id) referenced_tables
from sys.foreign_keys fk
    inner join sys.tables fk_tab
        on fk_tab.object_id = fk.parent_object_id
group by schema_name(fk_tab.schema_id) + '.' + fk_tab.name
order by count(*) desc

Columns

  • table - table with schema name
  • foreign_keys - number of foreign keys in a table
  • referenced_tables - number of referenced tables. Note that it is not the same as number of foreign keys, as multiple foreign keys may reference the same table.

Rows

  • One row represents one table in a database
  • Scope of rows: tables in a database that have foreign keys (reference other tables)
  • Ordered by number of foreign keys from the ones with the most

Sample results

Tables in AdventureWorks with most foreign keys:

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