亂馬客

DBCC CHECKIDENT : 檢查指定之資料表目前的識別值，必要的話，會變更識別值。 您也可以使用 DBCC CHECKIDENT，手動設定識別欄位的新目前識別值。

以下的語法是將 Person.AddressType 資料表的identity 欄位重設成 10，所以AddressType新增一筆資料時，identity欄位會變成11。

DBCC CHECKIDENT ("Person.AddressType", RESEED, 10);

 

 

所以如果我們要將該資料庫中所有Table的identity欄位重設成1的話，可透過 sp_MSforeachtable 來執行 DBCC CHECKIDENT，如下，

--重設所有TABLE的IDENTITY 值為 1
EXEC sp_MSforeachtable  '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'

 

 

 

以下為測試的Script，

--建立T1
CREATE TABLE [dbo].[T1](
    [C1] [int] IDENTITY(1,1) NOT NULL,
    [C2] [nchar](10) NULL,
 CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED 
([C1] ASC))  

--建立T2
CREATE TABLE [dbo].[T2](
    [C1] [int] IDENTITY(1,1) NOT NULL,
    [C2] [nchar](10) NULL,
 CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED 
([C1] ASC))  

INSERT INTO dbo.T1( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'3' );

INSERT INTO dbo.T2( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'3' );

DBCC CHECKIDENT ('T1', NORESEED);
DBCC CHECKIDENT ('T2', NORESEED);
/*
目前的值都是3
Checking identity information: current identity value '3', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
--雖然刪除了Table，但是identity的值還是沒變
DELETE FROM dbo.T1;
DELETE FROM dbo.T2;

INSERT INTO dbo.T1( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'3' );

INSERT INTO dbo.T2( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'3' );

SELECT * FROM dbo.T1;
SELECT * FROM dbo.T2;

DBCC CHECKIDENT ('T1', NORESEED);
DBCC CHECKIDENT ('T2', NORESEED);
/*
所以再加了3筆資料後，identity的值變成了6
Checking identity information: current identity value '6', current column value '6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
 
DELETE FROM dbo.T1;
DELETE FROM dbo.T2;

--重設所有TABLE的IDENTITY 值為 1
EXEC sp_MSforeachtable  '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 1)'


DBCC CHECKIDENT ('T1', NORESEED);
DBCC CHECKIDENT ('T2', NORESEED);
/*
所以T1, T2的identity的值都被設定成了1
Checking identity information: current identity value '1', current column value '1'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
INSERT INTO dbo.T1( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T1( C2 ) VALUES  ( N'3' );
 
INSERT INTO dbo.T2( C2 ) VALUES  ( N'1' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'2' );
INSERT INTO dbo.T2( C2 ) VALUES  ( N'3' );

SELECT * FROM dbo.T1;
SELECT * FROM dbo.T2;
--因為前面重設成1，所以下一筆資料從2開始


DROP TABLE dbo.T1;
DROP TABLE dbo.T2;

-- 如果遇到 TRUNCATE TABLE Example
CREATE TABLE T3 (
C1 INT IDENTITY(10,1) NOT NULL,
C2 nchar(10) NULL);

INSERT INTO T3 VALUES('10開始');
INSERT INTO T3 VALUES('目前11');
SELECT * FROM T3;
/*
10	10開始      
11	目前11   
*/

DBCC CHECKIDENT('T3', RESEED, 1);
/*
Checking identity information: current identity value '11', current column value '1'.
*/ 
INSERT INTO T3 VALUES('1再來是2');
SELECT * FROM T3;
/*
10	10開始      
11	目前11   
2	1再來是2 
*/

--再來TRUNCATE TABLE後，identity的值又會重設成10
--不會再受 DBCC CHECKIDENT 影響哦!
TRUNCATE TABLE T3;
INSERT INTO T3 VALUES('10開始');
SELECT * FROM T3;
/*
10	10開始      
*/

DROP TABLE T3;

 

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^