sql中删除某一列的约束（包括默认约束、主键约束、外键约束等）

--删除某字段的所有关系

DECLARE @tbname SYSNAME ,
    @fdname SYSNAME
SELECT  @tbname = 't' --要处理的表名
        ,
        @fdname = 'a'
 --要处理的字段名

--定义删除处理的语句
DECLARE tb CURSOR local FOR
--默认值约束
SELECT sql='alter table ['+b.name+'] drop constraint ['+d.name+']'
FROM syscolumns a
JOIN sysobjects b ON a.id=b.id AND a.name=@fdname AND b.name=@tbname
JOIN syscomments c ON a.cdefault=c.id
JOIN sysobjects d ON c.id=d.id
UNION --外键引用
SELECT s='alter table ['+c.name+'] drop constraint ['+b.name+']'
FROM sysforeignkeys a
JOIN sysobjects b ON b.id=a.constid
JOIN sysobjects c ON c.id=a.fkeyid
JOIN syscolumns d ON d.id=c.id AND a.fkey=d.colid AND d.name=@fdname
JOIN sysobjects e ON e.id=a.rkeyid AND e.name=@tbname
JOIN syscolumns f ON f.id=e.id AND a.rkey=f.colid
UNION --主键/唯一键/索引
SELECT CASE WHEN e.xtype IN('PK','UQ') THEN 'alter table ['+c.name+'] drop constraint ['+e.name+']'
ELSE 'drop index ['+c.name+'].['+a.name+']' END
FROM sysindexes a
JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid
JOIN sysobjects c ON b.id=c.id AND c.xtype='U' AND c.name=@tbname
JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid AND d.name=@fdname
LEFT JOIN sysobjects e ON e.id=OBJECT_ID(a.name)
WHERE a.indid NOT IN(0,255)

--执行删除
DECLARE @s VARCHAR(8000)
OPEN tb
FETCH NEXT FROM tb INTO @s
WHILE @@fetch_status = 0 
    BEGIN
        EXEC(@s)
        FETCH NEXT FROM tb INTO @s
    END
CLOSE tb
DEALLOCATE tb

（转自：鼠U猫）

转载于:https://www.cnblogs.com/garyxiao/archive/2013/02/19/2916545.html