Reseeding *All* Identity Values in a Database…

Reseeding All Identity Values in a Database

Jul 19, 2011 in database, work, code, and technology

(This post was written against SQL Server 2000. The concepts apply to more recent versions, but the batch script may not work on them.)

If you work intimately with databases for very long, you might run into an issue where you can’t insert a record because of some weird duplicate primary key error:

Msg 2627, Level 14, State 1, Line whatever        
Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object '<table>'.
The statement has been terminated.

“That’s strange,” you’ll say, because it’s failing on an identity column, which is supposed to auto-increment. Here’s a test case that demonstrates this behavior, and the fix:

CREATE TABLE IdentityTest (ID INT IDENTITY PRIMARY KEY)

-- insert auto-incrementing values
INSERT IdentityTest DEFAULT VALUES -- 1
INSERT IdentityTest DEFAULT VALUES -- 2
INSERT IdentityTest DEFAULT VALUES -- 3

SELECT * FROM IdentityTest -- 1, 2, 3

-- reseed the identity column
DBCC CHECKIDENT (IdentityTest, RESEED, -3)

-- insert more auto-incrementing values (note, things *work*...for a while)
INSERT IdentityTest DEFAULT VALUES -- -2
INSERT IdentityTest DEFAULT VALUES -- -1
INSERT IdentityTest DEFAULT VALUES -- 0
INSERT IdentityTest DEFAULT VALUES -- <font color="#ff0000">BOOM</font>

DBCC CHECKIDENT (IdentityTest, RESEED) -- fix!
INSERT IdentityTest DEFAULT VALUES -- phew!

SELECT * FROM IdentityTest -- 1, 2, 3, -2, -1, 0, 4

DROP TABLE IdentityTest

The easiest way to fix this is to reset (“reseed”) the table’s identity value:

DBCC CHECKIDENT (IdentityTest, RESEED) -- fix!

This script, pulled from RedGate’s forums (an awesome company, by the way), resets those identity values for every table in the database:

-- courtesy of RedGate forums http://bit.ly/plJNx2
DECLARE @table NVARCHAR(4000), @column NVARCHAR(4000)

DECLARE row CURSOR FOR
  SELECT 
    a.name TableName, 
    b.name IdentityColumn
  FROM sysobjects a
  JOIN syscolumns b
  ON a.id = b.id
  WHERE COLUMNPROPERTY(a.id, b.name, 'isIdentity') = 1
    AND OBJECTPROPERTY(a.id, 'isTable') = 1
    AND a.xtype='U'
  ORDER BY a.name
OPEN row

WHILE 1=1
BEGIN
  FETCH NEXT FROM row INTO @table, @column
  IF @@FETCH_STATUS = -1
     BREAK
  
  PRINT @table
  EXEC sp_executesql N'DBCC CHECKIDENT (@table, RESEED)', N'@table varchar(4000)', @table = @table   
  PRINT ''
END

CLOSE row
DEALLOCATE row

This is handy to have in the toolbox when a data sync screws up a bunch of tables. In fact, that’s the primary reason I’m posting it here—so I can refer back to it in the future as needed.