1、腳本說明
此腳本用于清空數(shù)據(jù)庫數(shù)據(jù)，只刪除相關(guān)表記錄，保留表結(jié)構(gòu)及存儲過程觸發(fā)器等主要架構(gòu)。
設(shè)計(jì)思路：
1）根據(jù)表添加時間逆向獲取所有用戶表信息
2）使用游標(biāo)循環(huán)刪除每張表內(nèi)數(shù)據(jù)
3）使用delete進(jìn)行刪除，即使有外鍵關(guān)系同樣可以刪除表記錄
4）表存在自增主鍵則將其重置為0
5）截?cái)?a target="_self" style="word-break: break-all; color: #202859; text-decoration: none; line-height: normal !important; ">日志，將數(shù)據(jù)庫表空間及日志文件縮減到最小
2、使用說明
1）建立刪除數(shù)據(jù)庫存儲過程SP_DaTaBaSeClear
以下是代碼片段：
If( object_id('SP_DaTaBaSeClear') is not null ) drop procedure SP_DaTaBaSeClear go SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE SP_DaTaBaSeClearASBegin Transaction declare @BtableName varchar(200) declare curDel cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc open curDel declare @delSQL varchar(500) fetch next from curDel into @BtableName while( @@fetch_status = 0) begin set @delSQL = 'delete from ' + @BtableName print @delSQL exec( @delSQL ) if( ident_seed(@BtableName) is not null ) begin dbcc checkident( @BtableName, reseed, 0 ) print '種子成功置為1' end fetch next from curDel into @BtableName end close curDel deallocate curDel Commit GO |
2）執(zhí)行該存儲過程，執(zhí)行過程中查看執(zhí)行信息，如有紅色信息則先手動刪除紅色信息表記錄
-- 執(zhí)行存儲過程刪除表數(shù)據(jù)
EXEC SP_DaTaBaSeClear
3）如仍然報(bào)出紅色信息則直接執(zhí)行以下語句進(jìn)行刪除
以下是代碼片段：
declare @BtableName varchar(128) declare curDel cursor for select rtrim(name) from sysobjects where type = 'U' order by crdate desc open curDel declare @delSQL varchar(255) fetch next from curDel into @BtableName while( @@fetch_status = 0) begin set @delSQL = 'delete from ' + @BtableName print @delSQL exec( @delSQL ) if( ident_seed(@BtableName) is not null ) begin dbcc checkident( @BtableName, reseed, 0 ) print '種子成功置為1' end fetch next from curDel into @BtableName end close curDel deallocate curDel |
4）最后執(zhí)行腳本重置數(shù)據(jù)庫大小
以下是代碼片段：
backup log @DataBaseName with no_log dbccshrinkdatabase(@DataBaseName)
dbccupdateusage(@DataBaseName) |