Reseeding Microsoft SQL Tables

Getting the current primary key seed value for a table

To get the next value from the primary key column of the database, either use the verbose descriptive CHECKIDENT method, or if you want to select it into a variable, use the IDENT_CURRENT function.

> DBCC CHECKIDENT ('TableName', NORESEED)
Checking identity information: current identity value '12345', current column value '56789'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

> SELECT IDENT_CURRENT('TableName')
12345

Updating the primary key seed for a table

You can use either CHECKIDENT again to set the value.

DBCC CHECKIDENT ('TableName', RESEED, 12345)

Updating the primary key seed automatically

Here's how to increment based on the highest existing primary key:

DECLARE @tableName VARCHAR(255); SET @tableName = 'TableName';
DECLARE @currentIdentity INT; SET @currentIdentity = (SELECT IDENT_CURRENT(@tableName))
DBCC CHECKIDENT (@tablename, RESEED, @currentIdentity);