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 = 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