--删除某字段的所有关系
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猫)