用SQLScript刪除Database中所有表中的記錄,允許指定表不処理
可以執行下麪的腳本來刪除相應DB中所有表的記錄,竝且允許指定一些不処理的表。
如果存在(select * from tempdb . dbo . sysobjects,其中名稱類似於' # tmp _ Sys _ DBS % ')
drop Table # tmp _ Sys _ DBS
Go
-創建一個新表來記錄表類別
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
)
-某些受FMIS保護的表
insert # tmp _ Sys _ DBS(Obj _ Name,Obj_Type 描述)值(' Stock_Detail ',' U ',' ')
Go
Declare @ vDeleteData varchar(100)
Declare @ vTableName varchar(100)
Declare @ vTableName 1 varchar(100)
Declare @ vCount int
Select @ vCount = 1
Declare @ cursorall tablename cursor
Set @ cursorall tablename = cursor for @ vCount) ' # Do not delete/Truncate Table:' @ vTableName 1
Else
Begin
Select @ vDeleteData = ' if Exists(Select * From ' @ vTableName ')'
Select @ vDeleteData = @ vDeleteData ' Truncate Table ' @ vTableName
exec(@ vDeleteData)
-Print ' delete/Truncate:' @ vDeleteData
Print - ** - '
0條評論