Summary of default constraints in Azure SQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-04-01

Table of Contents:

    Query below summarizes default constraints in a database listing all distinct default value definitions for columns with the number of their occurrence.


    select con.[definition] as default_definition,
           count(distinct t.object_id) as [tables],
           count(col.column_id) as [columns]
    from sys.objects t
    join sys.all_columns col
            on col.object_id = t.object_id
    left join sys.default_constraints con
            on con.parent_object_id = t.object_id
            and con.parent_column_id = col.column_id
    where t.type = 'U'
    group by con.[definition]
    order by [columns] desc, [tables] desc


    • default_definition - definition of default constraint (formula) and NULL for columns without a default value
    • tables - number of tables with this constraint (or number of tables with columns with no constraints for NULL row)
    • columns - number of columns with this particular constraint (or no constraint for NULL row)


    • One row represents one unique definition of default value
    • Scope of rows: all distinct definitions of default column values in a database and one NULL row for columns without constraints
    • Ordered by the number of occurrences from the most popular

    Sample results

    Different default column definitions in AdventureWorks database:

    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.