The query below lists tables with their number of foreign keys and the number of tables to which they refer.

See also:

• Tables with most relationships
• Most referenced tables.

Query

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

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, since several foreign keys may reference to 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 starting from those that have more

Sample results

Tables in the AdventureWorksLT database with more foreign keys: