The query below lists the 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 as con
    left outer join sys.objects as t
        on con.parent_object_id = t.object_id
    left outer join sys.all_columns as 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: the 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 the AdventureWorksLT database.