自动生成清空数据库的SQL语句(V2.0)

之前写的那脚本没有注意到这重置标识值的问题。

1、当我们向一个含有标识列的表插入数据后，再执行

Delete From TableName
DBCC Checkident ('TableName',Reseed,0)

然后插入新数据，开始值是从1开始。这样处理是正确的。

 2、当我们新建一个表，在没有插入数据，就执行

Delete From TableName
DBCC Checkident ('TableName',Reseed,0)

 然后插入新数据，开始值就会从0开始。这样的结果，就不是我们预期要求的结果了。

下面做一个测试：

第一种情况：

Use test
Go
Set Nocount On
If Object_id('test') Is Not null
    Drop Table test
Go
Create Table test(id int Identity(1,1))
Insert test Default Values
Insert test Default Values
Insert test Default Values

Delete From test
Dbcc Checkident ('test',reseed,0)

Insert test Default Values

Dbcc Checkident ('test',noreseed)

Select * From test
/*
检查标识信息: 当前标识值 '3'，当前列值 '0'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

检查标识信息: 当前标识值 '1'，当前列值 '1'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

id
-----------
1
*/

第2种情况：

Use test
Go
Set Nocount On
If Object_id('test') Is Not null
    Drop Table test
Go
Create Table test(id int Identity(1,1))

Dbcc Checkident ('test',reseed,0)

Insert test Default Values

Dbcc Checkident ('test',noreseed)

Select * From test  --这里我们会发现开始值是0
/*
检查标识信息: 当前标识值 'NULL'，当前列值 '0'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
检查标识信息: 当前标识值 '0'，当前列值 '0'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
id
-----------
0


*/

为了解决这样的开始值为0问题，必须重置标识值为null

解决如下：

Use test
Go
Set Nocount On
If Object_id('test') Is Not null
    Drop Table test
Go
Create Table test(id int Identity(1,1))

Declare @null int  --借助一个null值的变量来重置表标识值为null
Dbcc Checkident ('test',reseed,@null)

Insert test Default Values


Dbcc Checkident ('test',noreseed)

Select * From test
/*
检查标识信息: 当前标识值 'NULL'，当前列值 'NULL'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
检查标识信息: 当前标识值 '1'，当前列值 '1'。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
id
-----------
1



*/

 因此，以前写的自动生成清空数据库的SQL语句脚本纠正如下：

/*自动生成清空数据库的SQL语句(V2.0) Andy 2008-10-8*/

Use Test

Go

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) End

From T3
Order By LevelNo
Option(Maxrecursion 0)
Print @Sql