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
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.