Reseeding MSSQL 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

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