I make heavy use of Red Gate’s excellent SQL Compare tools. I know I’m a bit of a shrill for them but they are time savers when dealing with multiple environments(development, testing, production) which is a pretty common occurrence in any sort of agile development. One flaw in them is that they often mess up the sequences in the destination database. Say you have a table Students with 15 records in it in development and 30 in production then performing a copy often brings along the sequence even if you don’t select syncing that table. This results in duplicate key errors whenever a new record is inserted.

For weeks I have been saying “I should write a script to check and fix that”. Well I finally did it

SET ANSINULLS ON
GO
SET QUOTED
IDENTIFIER ON
GO

create PROCEDURE dbo.FixTableIdentities

AS
BEGIN
SET NOCOUNT ON;
declare @currentKeyValue int
declare @currentIdentityValue int
declare @toRun nvarchar(500)
declare @tableToCheck nvarchar(500)
declare @idColumnCount int
declare db_cursor cursor for select name from sysobjects where type='U'

open dbcursor
fetch next from db
cursor into @tableToCheck

while @@FETCHSTATUS = 0
BEGIN
select @idColumnCount = count(*) from syscolumns where id=object
id(@tableToCheck) and name='id'
if(@idColumnCount = 1)
BEGIN
select @currentKeyValue = identcurrent(@tableToCheck)
set @toRun = N'select @currentIdentityValue = max(id) from ' + @tableToCheck;
EXEC sp
executesql @toRun, N'@currentIdentityValue int OUTPUT', @currentIdentityValue OUTPUT;
if(@currentIdentityValue @currentKeyValue)
BEGIN
DBCC CHECKIDENT (@tableToCheck,reseed, @currentIdentityValue)
END
END
FETCH NEXT FROM dbcursor into @tableToCheck
END
CLOSE db
cursor
deallocate db_cursor
END
GO

When run this procedure will go through all your tables and ensure that the id column is in sync with the sequence. At the moment it just looks at the column called id and manipulates that.