How to automatically reseed after using identity_insert?

Question

Hello, I recently migrated from a PostgreSQL database to a SQL Server database. To switch the data over I had to enable IDENTITY_INSERT. Well come to find out that I get all sorts of strange errors due to duplicate identity values(which are set as primary keys) upon doing an insert in any of the tables.

I have quite a few tables. What would be the easiest way of automatically reseeding the identity of every table so that it is after max(RID)?

Answer 1

Use the information in this link in combination with a SQL function that gets the max(RID) from each table that you need to reset. For instance, if you want to start your primary key seed at 25000, use the code below (StartSeedValue - 1)

DBCC CHECKIDENT('myTable', RESEED, 24999)

So in combination, you should end up with somethink like this

DECLARE @maxVal INT
SELECT @maxVal = ISNULL(max(ID),0)+1 from mytable
DBCC CHECKIDENT('mytable', RESEED, @maxVal)

How to Reseed SQL

Sorry for the Pseudo-code, been awhile since I have written a SQL function :)

EDIT:

Thanks for the catch, changed the INTEGER to INT

USE YourDBName
GO 
SELECT *
FROM sys.Tables
GO 

This will give you a listing of all user tables in the database. Use this query as your 'loop' and that should allow to reset the seeds on all tables.