Query below lists table default constraints.
Query
select schema_name(t.schema_id) + '.' + t.[name] as [table],
col.column_id,
col.[name] as column_name,
con.[definition],
con.[name] as constraint_name
from sys.default_constraints con
left outer join sys.objects t
on con.parent_object_id = t.object_id
left outer join sys.all_columns col
on con.parent_column_id = col.column_id
and con.parent_object_id = col.object_id
order by schema_name(t.schema_id) + '.' + t.[name],
col.column_id
Columns
- table - schema and table name
- column_id - column table id (unique within table)
- column_name - name of the column
- definition - SQL expression that defines this default constraint
- constraint_name - name of the constraint in the database
Rows
- One row represents one default constraint
- Scope of rows: query returns all default constraints in the database (all columns with default constraints)
- Ordered by table schema and name, table column id
Sample results
List of table default constraints in AdventureWorks database.