利用sql批量删除表，存储过程

利用sql批量删除表，存储过程。 最近用godaddy的空间，由于系统里面的表多，一个个的删除很麻烦，就网上搜集了一下解决方法。 给大家分享一下： 1.批量删除存储过程 declare @procName varchar(500) declare cur cursor for select [name] from sys.objects where type = p open cur fetch next from cur into @procName while @@fetch_status = 0 begin if @procName <> DeleteAllProcedures exec(drop procedure + @procName) fetch next from cur into @procName end close cur deallocate cur 2，批量删除外键 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 3.批量删除表 DECLARE c2 cursor for select drop table [+name +]; from sysobjects where xtype = u open c2 declare @c2 varchar(8000) fetch next from c2 into @c2 while(@@fetch_status=0) begin exec(@c2) fetch next from c2 into @c2 end close c2 deallocate c2 --批量清除表内容： --1.禁用外键约束 DECLARE c1 cursor for select alter table [+ object_name(parent_obj) + ] nocheck 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 c2 cursor for select truncate table [+name +]; from sysobjects where xtype = u open c2 declare @c2 varchar(8000) fetch next from c2 into @c2 while(@@fetch_status=0) begin exec(@c2) fetch next from c2 into @c2 end close c2 deallocate c2 --3.启用外键约束 DECLARE c1 cursor for select alter table [+ object_name(parent_obj) + ] check 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

转载于:https://www.cnblogs.com/stragon/p/3140658.html