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:

There are no comments. Click here to write the first comment.