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
SET QUOTEDIDENTIFIER ON
create PROCEDURE dbo.FixTableIdentities
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'
fetch next from dbcursor into @tableToCheck
while @@FETCHSTATUS = 0
select @idColumnCount = count(*) from syscolumns where id=objectid(@tableToCheck) and name='id'
if(@idColumnCount = 1)
select @currentKeyValue = identcurrent(@tableToCheck)
set @toRun = N'select @currentIdentityValue = max(id) from ' + @tableToCheck;
EXEC spexecutesql @toRun, N'@currentIdentityValue int OUTPUT', @currentIdentityValue OUTPUT;
DBCC CHECKIDENT (@tableToCheck,reseed, @currentIdentityValue)
FETCH NEXT FROM dbcursor into @tableToCheck
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.