Tuesday, October 20, 2009

RESEED all tables with Identity Columns in a Database

I had a problem today where a comparison tool had inserted lots of data into a database and it was then propagated to other environments. We started seeing errors and needed to ensure that all tables were fixed. This was the hack I came up with. Enjoy

DECLARE @SQL NVARCHAR(MAX)

DECLARE reseed CURSOR FAST_FORWARD FOR
SELECT N'DBCC CHECKIDENT (' + Quotename(Object_name(object_id)) + N',RESEED)'
FROM sys.columns
WHERE is_identity = 1
AND Objectproperty(object_id,'IsTable') = 1

OPEN reseed

FETCH NEXT FROM reseed
INTO @SQL

WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
EXEC dbo.Sp_executesql
@SQL
END

FETCH NEXT FROM reseed
INTO @SQL
END

CLOSE reseed
DEALLOCATE reseed


No comments: