用函數生成腳本:
--自动生成数据库的清空脚本(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
2005自动生成数据库的清空脚本
最新推荐文章于 2025-12-10 22:03:01 发布
本文介绍了一个使用T-SQL编写的脚本生成器，该生成器能够自动生成用于清空特定数据库中所有表的SQL脚本。通过递归遍历外键关系并根据表之间的依赖关系正确排序，确保了数据的一致性和安全性。
扫一扫
5619
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
