Summary of default constraints in SQL Server database

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


    con.[definition] as default_definition,
    count(distinct t.object_id) as [tables],
    count(col.column_id) as [columns]
from sys.objects t
    inner join sys.all_columns col
        on col.object_id = t.object_id
    left outer 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:

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.