List all default constraints in Azure SQL Database

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

Table of Contents:


    Article for: Azure SQL Database SQL Server MySQL MariaDB

    The query below lists the default constraints defined in the database, ordered by constraint name.

    Check this query to see them organized by table.

    Query

    select con.[name] as constraint_name,
        schema_name(t.schema_id) + '.' + t.[name]  as [table],
        col.[name] as column_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
    order by con.name
    

    Columns

    • constraint_name - name of the constraint in the database
    • table - schema and table name for which the constraint is defined
    • column_name - name of the column
    • definition - SQL expression that defines this default constraint

    Rows

    • One row: represents one default constraint
    • Scope of rows: the query returns all default constraints in the database (all columns with default constraints)
    • Ordered by constraint name

    Sample results

    List of default 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