有时候需要把数据库中表记录都删除掉，为了方便我们有必要制作一个清空数据库的脚本，
这里主要是考虑存在外键约束和标识列问题。
存在外键关系时候就要查询出，先要删除哪一张表，后要删除哪一张表，
当存在标识列的时候，就要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]
*/
