List all tables referenced by a specific table (by FK) in Azure SQL Database

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

Table of Contents:


    The query below lists all tables referenced with a foreign key per specific table.

    Query

    select distinct
        schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
        '>-' as rel,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table
    from sys.foreign_keys as fk
        inner join sys.tables as fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables as pk_tab
            on pk_tab.object_id = fk.referenced_object_id
    where fk_tab.[name] = 'Your table' -- enter table name here
    --  and schema_name(fk_tab.schema_id) = 'Your table schema name'
    order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name
    

    Columns

    • foreign_table - foreign table name with schema name - the table you provided as a parameter
    • foreign_table - relationship symbol implicating FK and direction
    • primary_table - primary (referenced) tables names with schema name - the tables you are looking for

    Rows

    • One row: represents one referenced table
    • Scope of rows: all tables referenced by the table that has the provided name (and optionally the schema)
    • Ordered by: referenced table schema and name

    Notes

    • There can be more tables with the same name. If that's the case, uncomment the where clause and provide a schema name

    Sample results

    All tables referenced with FK by Product table in the AdventureWorksLT database.

    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