List table check constraints in Azure SQL Database

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

Table of Contents:


    The query below lists table check constraints.

    Query

    select schema_name(t.schema_id) + '.' + t.[name] as [table],
        col.column_id,
        col.[name] as column_name,
        con.[definition],
        case when con.is_disabled = 0
            then 'Active'
            else 'Disabled'
            end as [status],
        con.[name] as constraint_name
    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
    order by schema_name(t.schema_id) + '.' + t.[name],
        col.column_id
    

    Columns

    • table - schema and table name
    • column_id - column table id (unique within table)
    • column_name - name of the column for column-level check constraints, null for table-level check constraints
    • definition - SQL expression that defines this check constraint
    • status - constraint status:
      • 'Active' if constraint is active,
      • 'Disabled' for disabled constraints
    • constraint_name - name of the constraint in the database

    Rows

    • One row: represents one check constraint
    • Scope of rows: the query returns all check constraints in the database
    • Ordered by: table schema and name, table column id

    Sample results

    List of table check constraints in the AdventureWorksLT database.

    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