批量删除MSSQLServer的外键约束

SQLServer2005/2008 测试通过.

--创建临时表
create table #t (keyId int identity,name varchar(200),tbname varchar(200))

--将所有的外键插入临时表中
insert into #t (name,tbName) 
select a.name,b.name from sys.foreign_keys a left join sysobjects b on a.parent_object_id = b.id 

declare @i int,@imax int,@name varchar(200) ,@tbName varchar(200)

select @i=MIN(keyId) from #t 
select @imax=MAX(keyid) from #t

while @i<=@imax
begin
	set @name='' 
	set @tbName =''
	select @name =name,@tbName=tbname from #t where keyId=@i

	--打印删除外键的语句,并执行
	print(' ALTER TABLE ['+@tbname +'] DROP CONSTRAINT ['+@name+']')
	exec(' ALTER TABLE ['+@tbname +'] DROP CONSTRAINT ['+@name+']')

	set @i=@i+1
end


truncate table #t 
drop table #t