List tables with most relationships in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2018-12-10

Table of Contents:


    The query below lists tables with most relationships (both foreign keys and FK references from other tables).

    See also:

    Query


    select tab as [table], count(distinct rel_name) as relationships, count(distinct fk_name) as foreign_keys, count(distinct ref_name) as [references], count(distinct rel_object_id) as related_tables, count(distinct referenced_object_id) as referenced_tables, count(distinct parent_object_id) as referencing_tables from (select schema_name(tab.schema_id) + '.' + tab.name as tab, fk.name as rel_name, fk.referenced_object_id as rel_object_id, fk.name as fk_name, fk.referenced_object_id, null as ref_name, null as parent_object_id from sys.tables as tab left join sys.foreign_keys as fk on tab.object_id = fk.parent_object_id union all select schema_name(tab.schema_id) + '.' + tab.name as tab, fk.name as rel_name, fk.parent_object_id as rel_object_id, null as fk_name, null as referenced_object_id, fk.name as ref_name, fk.parent_object_id from sys.tables as tab left join sys.foreign_keys as fk on tab.object_id = fk.referenced_object_id) as q group by tab order by count(distinct rel_name) desc

    Columns

    • table - name of table with its schema name
    • relationships - number of relationships (FKs and FK references)
    • foreign_keys - number of foreign keys in a table
    • references - number of FK references from other tables
    • related_tables - number of different related tables (regardless of the relationship type/direction, the table is counted only once)
    • referenced_tables - number of different tables referenced with FKs (please note that the table can be related more than once, so the number of FKs and number of referenced tables can be different)
    • referencing_tables - number of different tables referencing with foreign keys this table

    Rows

    • One row: represents one table in a database
    • Scope of rows: all tables in a database
    • Ordered by: number of relationships (foreign keys and references) from the ones with the most

    Sample results

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

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy.
    Accept