List table columns with their foreign keys in Azure SQL Database

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

Table of Contents:

    The query below returns all columns from all tables in a database with a foreign key reference if the column has one.


    select schema_name(tab.schema_id) + '.' + as [table],
        col.column_id, as column_name,
        case when fk.object_id is not null then '>-' else null end as rel,
        schema_name(pk_tab.schema_id) + '.' + as primary_table, as pk_column_name,
        fk_cols.constraint_column_id as no, as fk_constraint_name
    from sys.tables as tab
        inner join sys.columns as col
            on col.object_id = tab.object_id
        left outer join sys.foreign_key_columns as fk_cols
            on fk_cols.parent_object_id = tab.object_id
                and fk_cols.parent_column_id = col.column_id
        left outer join sys.foreign_keys as fk
            on fk.object_id = fk_cols.constraint_object_id
        left outer join sys.tables as pk_tab
            on pk_tab.object_id = fk_cols.referenced_object_id
        left outer join sys.columns as pk_col
            on pk_col.column_id = fk_cols.referenced_column_id
                and pk_col.object_id = fk_cols.referenced_object_id
    order by schema_name(tab.schema_id) + '.' +,


    • table - table in a database with schema name
    • column_id - number of the column in a database
    • column_name - name of the column
    • rel - relationship symbol ('>-') indicating foreign key and direction
    • primary_table - referenced table
    • pk_column_name - referenced column
    • no - column id in a key constraint
    • fk_constraint_name - foreign key constraint name


    • One row: represents one column of every table in a database
    • Scope of rows: all columns from all tables in a database
    • Ordered by: table schema and name, column id in a table

    Sample results

    A few columns in the AdventureWorksLT database with their foreign keys:

    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.