Query below lists tables that are most referenced by other tables with foreign keys. Those are the dictionary tables such as person, product or store. In data warehouses those are dimension tables.
- Tables with most relationships ,
- Tables with most foreign keys ,
- Summary article of FK queries for SQL Server.
select schema_name(tab.schema_id) + '.' + tab.name as [table], count(fk.name) as [references], count(distinct fk.parent_object_id) as referencing_tables from sys.tables as tab left join sys.foreign_keys as fk on tab.object_id = fk.referenced_object_id group by schema_name(tab.schema_id), tab.name having count(fk.name) > 0 order by 2 desc
- table - name of the table with schema name
- references - number of foreign keys referencing to this table
- referencing_tables - number of different tables referencing to this table
- One row represents one table in a database
- Scope of rows: tables being used in foreign keys as primary key table
- Ordered by number of foregin keys descending
List of most referenced tables in AdventureWorks database. Production.Product table is referenced 14 times from 13 different tables.