List tables with the most foreign keys in Azure SQL Database

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

See also:

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:

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