Press enter to see results or esc to cancel.

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