maintenance/mssql/archives/named_constraints.sql

   1 DECLARE @fullyQualifiedTableName nvarchar(max),
   2 @tableName sysname,
   3 @fieldName sysname,
   4 @constr sysname,
   5 @constrNew sysname,
   6 @sqlcmd nvarchar(max),
   7 @sqlcreate nvarchar(max)
   8
   9 SET @fullyQualifiedTableName = '/*_*//*$tableName*/'
  10 SET @tableName = '/*$tableName*/'
  11 SET @fieldName = '/*$fieldName*/'
  12
  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) + '%')
  20
  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')
  28
  29 IF @constr IS NOT NULL
  30 BEGIN
  31   SET @sqlcmd =  'ALTER TABLE ' + @fullyQualifiedTableName + ' DROP CONSTRAINT [' + @constr + ']'
  32   EXECUTE sp_executesql @sqlcmd
  33 END
  34 IF @constrNew IS NULL
  35 BEGIN
  36   SET @sqlcreate =  'ALTER TABLE ' + @fullyQualifiedTableName + ' WITH NOCHECK ADD CONSTRAINT ' + @fieldName + '_ckc CHECK /*$checkConstraint*/;'
  37   EXECUTE sp_executesql @sqlcreate
  38 END