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.

    Query

    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
    

    Columns

    • 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)

    Rows

    • 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:

    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