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:
Post a Comment