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.


    select schema_name(fk_tab.schema_id) + '.' + as foreign_table,
        '>-' as rel,
        schema_name(pk_tab.schema_id) + '.' + as primary_table,
        fk_cols.constraint_column_id as no, as fk_column_name,
        ' = ' as [join], as pk_column_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) + '.' +,
        schema_name(pk_tab.schema_id) + '.' +,


    • 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


    • 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:

    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.