List foreign keys in Azure SQL Database

Rene Castro - Dataedo Team Rene Castro 2019-01-28

Table of Contents:


    The query below returns foreign key constraints 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,
        substring(column_names, 1, len(column_names)-1) as [fk_columns],
        fk.name as fk_constraint_name
    from sys.foreign_keys fk
        inner join sys.tables fk_tab
            on fk_tab.object_id = fk.parent_object_id
        inner join sys.tables pk_tab
            on pk_tab.object_id = fk.referenced_object_id
        cross apply (select col.[name] + ', '
                        from sys.foreign_key_columns fk_c
                            inner join sys.columns col
                                on fk_c.parent_object_id = col.object_id
                                and fk_c.parent_column_id = col.column_id
                        where fk_c.parent_object_id = fk_tab.object_id
                          and fk_c.constraint_object_id = fk.object_id
                                order by col.column_id
                                for xml path ('') ) D (column_names)
    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
    • rel - relationship symbol implicating direction
    • primary_table - primary (referenced) table name with schema name
    • fk_constraint_name - foreign key constraint name

    Rows

    • One row: represents one foreign key constraint.
    • Scope of rows: all foreign key constraints in a database
    • Ordered by: foreign table schema name and table name

    Sample results

    Foreign keys in the AdventureWorksLT database:

    You could also get this

    Get this interactive HTML data dictionary in minutes with Dataedo.

    See live HTML data dictionary sample

    Try for free

    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