The query below lists tables that are most referenced by other tables with foreign keys. Those are the dictionary tables such as Address or Customer. In a data warehouses those are dimension tables.

See also:

• Tables with most relationships
• Tables with most foreign keys

Query

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

Columns

• 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

Rows

• 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 foreign keys in descending order

Sample results

List of most referenced tables in the AdventureWorksLT database. SalesLT.Address table is referenced 3 times from 2 different tables.