可执行以下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! --**--'
责任编辑：小草
