循环删除外键约束

SQL删除,有外键约束常常不能删除,需要先删除外键

 

--/***********PowerBy:rovedog ***************/
--/第1步**********删除所有表的外键约束*************************/

DECLARE c1 CURSOR  FOR 
SELECT  'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
FROM  sysobjects
WHERE  xtype = 'F'
OPEN  c1
DECLARE  @c1 varchar(8000)
FETCH  NEXT  FROM  c1 INTO  @c1
WHILE (@@fetch_status=0)
BEGIN 
EXEC (@c1)
FETCH  NEXT  FROM  c1 INTO  @c1
END 
CLOSE  c1
DEALLOCATE  c1

--/第2步**********删除所有表*************************/

DECLARE  @sql varchar(8000)
WHILE  (select count(*) FROM  sysobjects WHERE  type='U')>0
BEGIN 
SELECT @sql='drop table [' + name+']'
FROM sysobjects
WHERE (type = 'U')
ORDER BY 'drop table [' + name+']'
EXEC (@sql) 
END 

 常常在PD中设置如下

posted @ 2020-03-01 07:58  猫狼  阅读(185)  评论(0编辑  收藏  举报