/*自动生成清空数据库的SQL语句(V2.0) Andy 2008-10-8*/Use TestGo Declare @Sql nvarchar(Max) ;With T1 As ( Select Convert(int,0) as LevelNo,fkeyid,rkeyid From sys.sysforeignkeys a Where Not Exists(Select 1 From sys.sysforeignkeys Where rkeyid=a.fkeyid) Union All Select b.LevelNo+1,a.fkeyid,a.rkeyid From sys.sysforeignkeys a,T1 b Where a.fkeyid=b.rkeyid ) ,T2 As ( Select LevelNo,id=fkeyid From T1 Union All Select LevelNo+1,rkeyid From T1 ) ,T3 As ( Select a.name,LevelNo=Max(Isnull(b.LevelNo,0)),c.is_identity From sys.sysobjects a Left Outer Join T2 b On a.id=b.id Left Outer Join sys.identity_columns c On c.object_id=a.id Where a.xtype='U' And a.name<>'sysdiagrams' Group By a.name,c.is_identity
) Select @Sql=Isnull(@Sql,'Use'+Quotename(Db_name())+Char(13)+Char(10)+'Declare @null int ')+char(13)+char(10)+ Case When LevelNo=0 Then 'Truncate Table '+Quotename(name) When is_identity=1 Then 'Delete From '+Quotename(name)+' DBCC Checkident ('''+Quotename(name)+''',Reseed,@null)' Else 'Delete From '+Quotename(name) EndFrom T3 Order By LevelNo Option(Maxrecursion 0) Print @Sql
1: sp_MSforeachtable @command1='Delete from ?'
2: sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"
用sp_MSForeachTable 搭配truncate table命令
2000 可以
2005 不能用
Declare @Sql nvarchar(Max)
;With T1
As
(
Select Convert(int,0) as LevelNo,fkeyid,rkeyid
From sys.sysforeignkeys a
Where Not Exists(Select 1 From sys.sysforeignkeys Where rkeyid=a.fkeyid)
Union All
Select b.LevelNo+1,a.fkeyid,a.rkeyid
From sys.sysforeignkeys a,T1 b
Where a.fkeyid=b.rkeyid
)
,T2
As
(
Select LevelNo,id=fkeyid From T1
Union All
Select LevelNo+1,rkeyid From T1
)
,T3
As
(
Select a.name,LevelNo=Max(Isnull(b.LevelNo,0)),c.is_identity
From sys.sysobjects a
Left Outer Join T2 b On a.id=b.id
Left Outer Join sys.identity_columns c On c.object_id=a.id
Where a.xtype='U' And a.name<>'sysdiagrams'
Group By a.name,c.is_identity
)
Select @Sql=Isnull(@Sql,'Use'+Quotename(Db_name())+Char(13)+Char(10)+'Declare @null int ')+char(13)+char(10)+
Case When LevelNo=0 Then 'Truncate Table '+Quotename(name)
When is_identity=1 Then 'Delete From '+Quotename(name)+' DBCC Checkident ('''+Quotename(name)+''',Reseed,@null)'
Else 'Delete From '+Quotename(name) EndFrom T3
Order By LevelNo
Option(Maxrecursion 0)
Print @Sql