最近用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
?
?
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
