1 DECLARE @fullyQualifiedTableName
nvarchar(max),
7 @sqlcreate
nvarchar(max)
9 SET @fullyQualifiedTableName
= '/*_*//*$tableName*/'
10 SET @tableName
= '/*$tableName*/'
11 SET @fieldName
= '/*$fieldName*/'
13 SELECT @constr
= CONSTRAINT_NAME
14 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
15 WHERE TABLE_NAME = @tableName
16 AND CONSTRAINT_CATALOG = '/*$wgDBname*/'
17 AND CONSTRAINT_SCHEMA = '/*$wgDBmwschema*/'
18 AND CONSTRAINT_TYPE
= 'CHECK'
19 AND CONSTRAINT_NAME LIKE ('CK__' + left(@tableName
,9) + '__' + left(@fieldName
,5) + '%')
21 SELECT @constrNew
= CONSTRAINT_NAME
22 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
23 WHERE TABLE_NAME = @tableName
24 AND CONSTRAINT_CATALOG = '/*$wgDBname*/'
25 AND CONSTRAINT_SCHEMA = '/*$wgDBmwschema*/'
26 AND CONSTRAINT_TYPE
= 'CHECK'
27 AND CONSTRAINT_NAME = (@fieldName
+ '_ckc')
29 IF @constr
IS NOT NULL
31 SET @sqlcmd
= 'ALTER TABLE ' + @fullyQualifiedTableName
+ ' DROP CONSTRAINT [' + @constr
+ ']'
32 EXECUTE sp_executesql @sqlcmd
36 SET @sqlcreate
= 'ALTER TABLE ' + @fullyQualifiedTableName
+ ' WITH NOCHECK ADD CONSTRAINT ' + @fieldName
+ '_ckc CHECK /*$checkConstraint*/;'
37 EXECUTE sp_executesql @sqlcreate