2010-06-28

Fixing Table Identities

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
SET ANSI_NULLS 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 db_cursor
fetch next from db_cursor into @tableToCheck
while @@FETCH_STATUS = 0
BEGIN
select @idColumnCount = count(*) from syscolumns where id=object_id(@tableToCheck) and name='id'
if(@idColumnCount = 1)
BEGIN
select @currentKeyValue = ident_current(@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 db_cursor 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.


comment: