[MSSQL]删除约束

来源：http://www.archonsystems.com/devblog/2012/05/25/how-to-drop-a-column-with-a-default-value-constraint-in-sql-server/

 

DECLARE @ConstraintName nvarchar(200);

SELECT @ConstraintName = Name
FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__')
  AND PARENT_COLUMN_ID = (
    SELECT column_id
    FROM SYS.COLUMNS
    WHERE NAME = N'__ColumnName__'
      AND object_id = OBJECT_ID(N'__TableName__')
  );
IF @ConstraintName IS NOT NULL
BEGIN
  EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName);
END

 

另一方法（修改字段类型）

IF EXISTS(SELECT name FROM SYSOBJECTS WHERE name = '__TableName__' AND xtype='U')
BEGIN
  DECLARE @name VARCHAR(100) = (
    SELECT TOP 1 name
    FROM SYSOBJECTS
    WHERE name LIKE 'DF\_\_%' ESCAPE '\' AND xtype='D' AND id=(SELECT TOP 1 cdefault FROM SYSCOLUMNS WHERE id=OBJECT_ID('__TableName__') AND name='__ColumnName__')
  );

  IF(@name IS NOT NULL)
  BEGIN
    EXEC('ALTER TABLE [DBO].[__TableName__] DROP CONSTRAINT ' + @name);
    EXEC('ALTER TABLE [DBO].[__TableName__] ALTER COLUMN [__ColumnName__] TEXT NULL');
  END;
  ELSE
  BEGIN
    ALTER TABLE [DBO].[__TableName__] ALTER COLUMN [__ColumnName__] TEXT NULL;
  END;
END;

PS：将__TableName__和__ColumnName__替换你的表名和字段名

 

SP_MSFOREACHDB
'
USE [?];

DECLARE @table VARCHAR(200)=''表名'';
DECLARE @column VARCHAR(200)=''字段名'';

IF OBJECT_ID(@table) IS NOT NULL
BEGIN
  IF (SELECT COUNT(*) FROM SYSCOLUMNS WHERE [ID]=OBJECT_ID(@table) AND [NAME]=@column)!=0
  BEGIN
    -- 删除约束
    DECLARE @ct VARCHAR(200);

    SELECT TOP 1 @ct=constraint_name
    FROM (
      -- 获取全部约束
      SELECT tab.name AS table_name,col.name AS column_name,obj.name AS constraint_name
      FROM SYSOBJECTS AS obj
        INNER JOIN (SELECT name,id FROM SYSOBJECTS Where XType=''U'') As tab ON tab.id=obj.parent_obj
        INNER JOIN SYSCONSTRAINTS AS const ON const.Constid=obj.id
        INNER JOIN SYSCOLUMNS AS col ON col.colid=const.colid AND col.id=tab.id
      -- ORDER BY 1,2
    ) AS tab
    WHERE tab.table_name=@table AND tab.column_name=@column

    IF @ct IS NOT NULL
      EXEC(''ALTER TABLE '' + @table + '' DROP CONSTRAINT '' + @ct);
    
    -- 删除字段
    EXEC(''ALTER TABLE '' + @table + '' DROP COLUMN '' + @column + '';'');
  END;
END;
';