Friday, October 17, 2014

View Default Constraints

A co-worker today couldn't figure out how a column, ostensibly being set to null held a value of 0 instead of null. I looked at the populating code and couldn't find anywhere a null was being replaced with a value of zero, so the most likely candidate was a default constraint on the column. Turned out he wasn't sure how to find default constraints on columns.

The easiest way is to use the sp_help function on the table name, or highlight the table name and hit alt-f1.

exec sp_help myTable

At the bottom, you'll find all the constraints on the table, including default constraints.

If however your table is not part of the dbo schema (as the Aventureworks sample database is fond of doing) you won't be able to use those methods on the table as it assumes the object exists in the 'dbo' schema. Instead, you can access the data via the Dynamic Management Views (DMVs) like this:
select 
    TableName = object_name(c.object_id),
    DefaultConstraintName = ds.name,
    ColumnName = c.name,
    DefaultValue = ds.[definition]
from sys.default_constraints ds
inner join sys.columns c
    on ds.parent_object_id = c.object_id
        and ds.parent_column_id = c.column_id


No comments:

Post a Comment