SQL存储过程之删除指定表中指定字段所有约束

CREATE PROCEDURE dropconstraint@tb varchar(100),@fd varchar(100)ASdeclare @tbname sysname,@fdname sysname select @tbname=@tb --要处理的表名 ,@fdname=@fd --要处理的字段名--定义删除处理的语句 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

先执行上面的SQL语句，创建存储过程，然后再执行存储过程即可，如：

--删除字段约束(表，列)exec dbo.dropconstraint 'table','Minute'exec dbo.dropconstraint 'table','Hour'exec dbo.dropconstraint 'table','Day'GO