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

用函數生成腳本:

--自动生成数据库的清空脚本(SQL05環境)





use Test--指定數據庫

go

if object_id('F_Clear','TF') is not null

	drop function F_Clear

go

create Function F_Clear()

returns @T table(Clear_SQL nvarchar(200),TableName sysname)

as

begin 

;with Ta_tree(Lev,fkeyID,rkeyID)

as

(select 

	1, a.fkeyid,a.rkeyid

from 

	sys.sysforeignkeys  a

where

	not exists(select 1 from sys.sysforeignkeys  where rkeyid=a.fkeyid)

union all

select 

	b.Lev+1,a.fkeyid,a.rkeyid

from 

	sys.sysforeignkeys  a

join

	Ta_tree b on b.rkeyID=a.fkeyid)

,Ta_tree2

as

(select 

	Lev,ObjectID,row=row_number()over(partition by ObjectID order by Lev desc)

from 

	(select Lev,FkeyID as ObjectID from Ta_tree union all select Lev+1,rkeyID as ObjectID from Ta_tree) T

)

insert @T

select 

	[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.Name

from 

	sysobjects a

left join

	Ta_tree2 b on a.ID=b.ObjectID and b.row=1

left join

	sys.identity_columns c on a.ID=c.object_id

where

	Xtype='U'

order by case when b.ObjectID is null then 0 else 1 end,b.lev asc





return 

end

go

select * from F_Clear()

go

drop function F_Clear