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.
SELECT SchemaName =
,TableName =
,ColumnName =
,DefaultConstraint =
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 = ‘EffectiveDate’ AND
o.type = ‘U’ AND
dc.parent_object_id IS NULL
ORDER BY 1,2,3

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s