The query below lists tables with their number of foreign keys and the number of tables to which they refer.
select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as [table], count(*) as foreign_keys, count (distinct referenced_object_id) as referenced_tables from sys.foreign_keys as fk inner join sys.tables as 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
- 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, since several foreign keys may reference to the same table.
- 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 starting from those that have more
Tables in the AdventureWorksLT database with more foreign keys: