List all table constraints (PK, UK, FK, Check & Default) in Azure SQL Database

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

Table of Contents:


    Article for: Azure SQL Database SQL Server Teradata

    The query below lists all table (and view) constraints - primary keys, unique key constraints and indexes, foreign keys and check and default constraints.

    Query

    select table_view,
        object_type,
        constraint_type,
        constraint_name,
        details
    from (
        select schema_name(t.schema_id) + '.' + t.[name] as table_view,
            case when t.[type] = 'U' then 'Table'
                when t.[type] = 'V' then 'View'
                end as [object_type],
            case when c.[type] = 'PK' then 'Primary key'
                when c.[type] = 'UQ' then 'Unique constraint'
                when i.[type] = 1 then 'Unique clustered index'
                when i.type = 2 then 'Unique index'
                end as constraint_type,
            isnull(c.[name], i.[name]) as constraint_name,
            substring(column_names, 1, len(column_names)-1) as [details]
        from sys.objects as t
            left outer join sys.indexes as i
                on t.object_id = i.object_id
            left outer join sys.key_constraints as c
                on i.object_id = c.parent_object_id
                and i.index_id = c.unique_index_id
           cross apply (select col.[name] + ', '
                            from sys.index_columns as ic
                                inner join sys.columns as col
                                    on ic.object_id = col.object_id
                                    and ic.column_id = col.column_id
                            where ic.object_id = t.object_id
                                and ic.index_id = i.index_id
                                    order by col.column_id
                                    for xml path ('') ) as D (column_names)
        where is_unique = 1
        and t.is_ms_shipped <> 1
        union all
        select schema_name(fk_tab.schema_id) + '.' + fk_tab.name as foreign_table,
            'Table',
            'Foreign key',
            fk.name as fk_constraint_name,
            schema_name(pk_tab.schema_id) + '.' + pk_tab.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
        union all
        select schema_name(t.schema_id) + '.' + t.[name],
            'Table',
            'Check constraint',
            con.[name] as constraint_name,
            con.[definition]
        from sys.check_constraints as con
            left outer join sys.objects as t
                on con.parent_object_id = t.object_id
            left outer join sys.all_columns as col
                on con.parent_column_id = col.column_id
                and con.parent_object_id = col.object_id
        union all
        select schema_name(t.schema_id) + '.' + t.[name],
            'Table',
            'Default constraint',
            con.[name],
            col.[name] + ' = ' + con.[definition]
        from sys.default_constraints as con
            left outer join sys.objects as t
                on con.parent_object_id = t.object_id
            left outer join sys.all_columns as col
                on con.parent_column_id = col.column_id
                and con.parent_object_id = col.object_id) as a
    order by table_view, constraint_type, constraint_name
    

    Columns

    • table_view - table or view schema and name
    • object_type - object type:
      • Table
      • View
    • constraint_type - type of constraint:
      • Primary key
      • Unique key
      • Foreign key
      • Check constraint
      • Default constraint
    • constraint_name - name of constraint or index
    • details - details of this constraint:
      • Primary key - PK column(s)
      • Unique key - UK column(s)
      • Foreign key - parent table name
      • Check constraint - check definition
      • Default constraint - column name and default value definition

    Rows

    • One row: represents one constraint: PK, UK, FK, Check, Default
    • Scope of rows: all constraints
    • Ordered by: schema, table name, constraint type

    Sample results

    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