Reset Identity Seeds

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspResetIdentities]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[uspResetIdentities]
go

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE uspResetIdentities

/* 
Simplified, re-entrant, revised version...
This procedure resets the identity seeds for all user tables to one plus
the current maximum value of the identity field. This is useful after doing
extensive debugging and roll-backs, during which identity seeds can be incremented
with discarded values not available for reuse unless something like this proc is used.
Jeff Roughgarden, MCSD, MCDBA

begin transaction
    exec uspResetIdentities
rollback transaction

*/as
set nocount on 

declare curIden cursor read_only for 
    SELECT OB.name AS [Table], COL.name AS [Field]
        FROM sysobjects OB 
            INNER JOIN syscolumns COL ON OB.id = COL.id
        WHERE (COLUMNPROPERTY(OB.id, COL.name, 'IsIdentity') = 1) 
            AND (OB.name <> 'dtproperties')
            and OB.xtype='U'

declare @strSQL nvarchar(500), @strParams nvarchar(255)
declare @strTableName nvarchar(100), @strFieldName nvarchar(100)
declare @intMax int; set @intMax=-1

open curIden
while (1=1) 
    begin
        fetch next from curIden into @strTableName, @strFieldName
        if @@fetch_status<>0 break
        ----
        set @strSQL='select @pintMax = 1 + isnull(max(' + @strFieldName + '),0) from ' + @strTableName 
        set @strParams = N'@pintMax bigint output'
        exec sp_executeSQL @strSQL, @strParams, @pintMax = @intMax output
        print ''
        DBCC CHECKIDENT (@strTableName, RESEED, @intMax)
        print '** Reset identity for ' + @strTableName + ' to ' + Cast(@intMax as varchar) + ' **'
    end
close curIden; deallocate curIden

set nocount off