这里主要是考虑存在外键约束，和标识列问题。
存在外键关系时候就要查询出，先要删除哪一张表，后要删除哪一张表，
当存在标识列的时候，就要DBCC Checkident 来重新设置标识值。
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 ) +
Case When LevelNo = 0 Then ' Truncate Table ' + Quotename (name)
When is_identity = 1 Then ' Delete From ' + Quotename (name) + ' DBCC Checkident ( ''' + Quotename (name) + ''' ,Reseed,0) '
Else ' Delete From ' + Quotename (name) End
From T3
Order By LevelNo
Print @Sql
生成示例
e.g:
/*
Use[Test]
Truncate Table [os]
Truncate Table [T2]
Truncate Table [t4]
Truncate Table [t5]
Truncate Table [test]
Delete From [T3]
Delete From [T1]
*/