Script to RESEED Database in SQL Server 2005

It is often required to RESEED the database tables with new range of identifiers especially when the table’s Primary Key column is designed with data types such as BIGINT/INT.

The following script allows us to RESEED the database tables with the new SEED value for all the user tables with identity column.

-- Declaration statement to capture the new SEED value
DECLARE @DesiredSeed VARCHAR(20)
SET @DesiredSeed = '5000000000'

DECLARE @TableName VARCHAR(256), @SQLStatement VARCHAR(1000)

-- Cursor to get the list of all user tables which have identity columns
DECLARE curIdentityTables CURSOR
FOR
SELECT b.TABLE_SCHEMA +'.'+ OBJECT_NAME (a.[id])
FROM sysobjects a INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON OBJECT_NAME(a.id) = b.TABLE_NAME and b.ORDINAL_POSITION = 1
WHERE OBJECTPROPERTY (a.[id], 'IsUserTable') = 1
and OBJECT_NAME (a.[id]) <> 'dtproperties'
and OBJECTPROPERTY (a.[id], 'TableHasIdentity') = 1
ORDER BY a.[name]

--Open the cursor to loop through the table list and reseed it
OPEN curIdentityTables
FETCH NEXT FROM curIdentityTables INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQLSTatement = 'DBCC CHECKIDENT (''' + @TableName + ''', RESEED, ' + @DesiredSeed + ')'
EXEC (@SQLSTatement)
FETCH NEXT FROM curIdentityTables INTO @TableName
END

CLOSE curIdentityTables
DEALLOCATE curIdentityTables