[Summary view] [Print] [Text view]

   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