…brew your own Business Intelligence

TSQL Script: Check for Missing DEFAULT Constraints

Here’s a quick script I put together recently while polishing up a data model I’ve been working on.  This script will list every table containing a column named EffectiveDate that is missing a default constraint.

The scenario that prompted its creation was for an EffectiveDate column that exists in a lot of the tables in this particular database.  Each instance should have a DEFAULT constraint so that the value defaults to GETDATE(). This script provides me with the information I need to go back into SSDT and correct the table definitions.
[sql] SELECT SchemaName = s.name
,TableName = o.name
,ColumnName = c.name
,DefaultConstraint = dc.name
FROM sys.objects o
INNER JOIN sys.columns c
ON c.object_id = o.object_id
INNER JOIN sys.schemas s
ON s.schema_id = o.schema_id
LEFT OUTER JOIN sys.default_constraints dc
ON dc.parent_object_id = o.object_id AND
dc.parent_column_id = c.column_id
WHERE c.name = ‘EffectiveDate’ AND
o.type = ‘U’ AND
dc.parent_object_id IS NULL
ORDER BY 1,2,3

Leave a Reply