List the most referenced tables in a database (by FK) in Azure SQL Database

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:

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.

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