用SQL Script 删除Database 中所有表中的记录，并且允许特别指定一些表不处理

可执行以下Script 来删除对应DB 中的所有Table 的记录，并且允许特别指定一些表不处理。

 

if exists ( select * from tempdb.dbo.sysobjects where name like '#tmp_Sys_DBS%' )
 drop table #tmp_Sys_DBS
Go

-- create one new table to record table category
Create Table [dbo].#tmp_Sys_DBS(
 Obj_ID int identity (1,1) Not Null ,
 Obj_Name sysname Not Null ,
 Obj_Type char(2) Not Null,
 Category_Code varchar(30) Not Null ,
 Description nvarchar(128) Null
)
-- some tables protected by FMIS
insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('SYSTEM_TABLE','U' , 'Protected' , 'System Parameter Table ')

-- Inventory Part
insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('Stock','U' , 'Inventory' , '')
insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('Stock_Detail','U' , 'Inventory' , '')

Go

Declare @vDeleteData varchar(100)
Declare @vTableName varchar(100)
Declare @vTableName1 varchar(100)
Declare @vCount int
Select @vCount=1
Declare @cursorAllTableName cursor
Set @cursorAllTableName = cursor for
 select name from sysobjects where type='U' Order by Name Desc
Open @cursorAllTableName
Fetch next from @cursorAllTableName Into @vTableName
While @@Fetch_Status=0
Begin
 Select @vTableName1 = Upper(Ltrim(Rtrim(@vTableName)))
 If Exists ( Select * From #tmp_Sys_DBS Where obj_name = @vTableName1 )
         PRINT 'XX> #'+ convert(varchar(10),@vCount)+'# Do not delete / truncate Table : ' + @vTableName1
 Else
 Begin
  select @vDeleteData = 'if Exists ( Select * From ' + @vTableName + ' ) '
  select @vDeleteData = @vDeleteData + '  Truncate Table '+@vTableName
  exec (@vDeleteData)
--  Print ' delete / truncate : ' + @vDeleteData
         PRINT '==> #'+ convert(varchar(10),@vCount)+'# Finished to delete / truncate Table : ' + @vTableName1
 End
 Fetch next from @cursorAllTableName Into @vTableName
 Select @vCount=@vCount+1
End
Close @cursorAllTableName
Deallocate @cursorAllTableName
Print '--**-- Complete successfully! --**--'