自动生成清空数据库的SQL语句 ----C#.NET 通用权限管理系统，.NET 开发者的福音

有时候需要把数据库中表记录都删除掉，为了方便我们有必要制作一个清空数据库的脚本，
这里主要是考虑存在外键约束，和标识列问题。
存在外键关系时候就要查询出，先要删除哪一张表，后要删除哪一张表，
当存在标识列的时候，就要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]
*/
C#.NET 通用权限管理系统 http://www.hairihan.com.cn/dotnet.html