自動產生清空資料庫的SQL語句

有時候需要把資料庫中表記錄都刪除掉，為了方便我們有必要製作一個清空資料庫的指令碼，
這裡主要是考慮存在外鍵約束，和識別欄位問題。
存在外鍵關係時候就要查詢出，先要刪除哪一張表，後要刪除哪一張表，
當存在識別欄位的時候，就要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]
*/