2005自动生成数据库的清空脚本

--自动生成数据库的清空脚本(SQL05環境)--use Test--指定數據庫--goif object_id('F_Clear','TF') is not nulldrop function F_ClearGO/****************************************************************************************************************************************************************%%函数名：F_Clear%%输入参数：%%输出参数：%%功能：****************************************************************************************************************************************************************%%编写：Roy(中国风)  ****************************************************************************************************************************************************************/create Function F_Clear()returns @T table(ID INT IDENTITY,Clear_SQL nvarchar(200),TableName sysname)asbegin ;with CTERK(Lev,fkeyID,rkeyID)as(select 1, a.fkeyid,a.rkeyidfrom sys.sysforeignkeys  awherenot exists(select 1 from sys.sysforeignkeys  where rkeyid=a.fkeyid)union allselect b.Lev+1,a.fkeyid,a.rkeyidfrom sys.sysforeignkeys  ajoinCTERK b on b.rkeyID=a.fkeyid),CTERK2as(select Lev,ObjectID,row=row_number()over(partition by ObjectID order by Lev DESC)from (select MAX(Lev) AS Lev,FkeyID as ObjectID from CTERK GROUP BY FkeyID union all select MAX(Lev)+1,rkeyID as ObjectID from CTERK GROUP BY rkeyID) AS a)insert @Tselect [Clear_SQL]=case when Lev=1 or b.ObjectID is null then 'Truncate table '+quotename(a.Name) when c.Object_id is not null then 'Delete '+quotename(a.Name)+char(13)+char(10)+'if @@rowcount>0 or IDENT_Current('''+a.Name+''')>1'+char(13)+char(10)+'dbcc checkident ('+quotename(a.Name,'''')+',Reseed,0)' else 'Delete '+quotename(a.Name) end,[TableName]=a.Namefrom sysobjects aleft joinCTERK2 b on a.ID=b.ObjectID and b.row=1left joinsys.identity_columns c on a.ID=c.object_idwhereXtype='U'order by case when b.ObjectID is null then 0 else 1 end,b.lev ascreturn endgoSELECT  a.[Clear_SQL]FROM    F_Clear() AS a        INNER JOIN sys.tables AS b ON b.name = a.tablenameORDER BY a.IDgodrop function F_Clear