Fix an MS-SQL default-value for table-column

Fix_Default_Value_MSSQL.sql

DECLARE @schemaName nvarchar(128); 
DECLARE @tableName nvarchar(128); 
DECLARE @columnName nvarchar(128); 
DECLARE @constraintName nvarchar(128); 
DECLARE @dropConstraintSQL nvarchar(MAX); 

SET @schemaName = 'dbo'; 
SET @tableName = 'T_ChecklistVersion'; 
SET @columnName = 'CLV_Obsoleted'; 


SET @constraintName = 
( 
	SELECT TOP 1 
		 -- sch.name, t.name,
		dc.name AS default_constraint_name 
	FROM sys.default_constraints AS dc 

	INNER JOIN sys.columns AS c 
		ON dc.parent_column_id = c.column_id 
		AND dc.parent_object_id = c.object_id 

	INNER JOIN sys.tables AS t 
		ON t.object_id = c.object_id 

	INNER JOIN sys.schemas AS sch 
		ON sch.schema_id = t.schema_id 

	WHERE (1=1) 
	AND sch.name = @schemaName 
	AND t.name = @tableName 
	AND c.name = @columnName 
); 

SET @dropConstraintSQL = N'ALTER TABLE ' 
+ QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) 
+ N' DROP CONSTRAINT ' + QUOTENAME(@constraintName) + N'; ' 
; 

-- DF_T_ChecklistVersion_CLV_Obsoleted
-- SELECT @dropConstraintSQL 
EXECUTE(@dropConstraintSQL); 


ALTER TABLE dbo.T_ChecklistVersion
ADD CONSTRAINT DF_T_ChecklistVersion_CLV_Obsoleted
DEFAULT '29991231' FOR CLV_Obsoleted;