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.

    Query

    select schema_name(tab.schema_id) + '.' + tab.name as [table],
        col.column_id,
        col.name as column_name,
        case when fk.object_id is not null then '>-' else null end as rel,
        schema_name(pk_tab.schema_id) + '.' + pk_tab.name as primary_table,
        pk_col.name as pk_column_name,
        fk_cols.constraint_column_id as no,
        fk.name 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) + '.' + tab.name,
        col.column_id
    

    Columns

    • 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

    Rows

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

    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