List foreign keys with columns in Azure SQL Database

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

Table of Contents:


    The query below returns foreign key constraint columns defined in a database.

    Query

    select 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,
        fk_cols.constraint_column_id as no, 
        fk_col.name as fk_column_name,
        ' = ' as [join],
        pk_col.name as pk_column_name,
        fk.name as fk_constraint_name
    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
        inner join sys.foreign_key_columns as fk_cols
            on fk_cols.constraint_object_id = fk.object_id
        inner join sys.columns as fk_col
            on fk_col.column_id = fk_cols.parent_column_id
                and fk_col.object_id = fk_tab.object_id
        inner join sys.columns as pk_col
            on pk_col.column_id = fk_cols.referenced_column_id
                and pk_col.object_id = pk_tab.object_id
    order by schema_name(fk_tab.schema_id) + '.' + fk_tab.name,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name,
        fk_cols.constraint_column_id
    

    Columns

    • foreign_table - foreign table name with schema name
    • rel - relationship symbol implicating direction
    • primary_table - primary (referenced) table name with schema name
    • no - id of the column in key. Single column keys always have 1, composite keys have 1, 2, ... n for each column of the key
    • fk_column_name - foreign table column
    • join - "=" symbol indicating join operation for pair of columns
    • pk_column_name - primary (referenced) table column
    • fk_constraint_name - foreign key constraint name

    Rows

    • One row: represents one foreign key column. If the foreign key consists of multiple columns (composite key), each column appears separately.
    • Scope of rows: all foreign keys in a database and their columns
    • Ordered by: foreign table schema name and table name and column ordinal position in the key

    Sample results

    Foreign key constraints in the AdventureWorksLT database with their columns:

    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