Script to set the minimum id for all tables in a sql server database

identity-baseline.sql

/*
    Script to set the minimum id for all tables.

    Note that new tables get @baseId as the next id, tables that have had data in get @baseId+1. Ref http://stackoverflow.com/a/13303429/10245
*/

set xact_abort on -- automatically rollback on error
set nocount on -- make it easier to spot errors by reducing output verbosity
BEGIN TRAN

-- configurable items:
-- new identity starting point for all tables
declare @baseId int = 10000;


-- set up the list of tables to process

declare @tables table (id int identity, name sysname, identCol sysname null, currentMax int, newSeed int);

insert into @tables (name, identCol)
    select tab.name, col.name
    from sys.tables tab 
    left outer join sys.identity_columns col on col.object_id = tab.object_id -- filter to only tables with identity columns
    order by tab.name;

update @tables set newSeed = @baseId where not identCol is null

declare @table sysname;
declare @col sysname;
declare @sql nvarchar(max);
declare @current int;
declare @newSeed int;

-- get current max id, decide whether to change identity seed
declare tableList cursor for
    select name, identCol, newSeed from @tables where not identCol is null
open tableList
fetch next from tableList into @table, @col, @newSeed
while @@FETCH_STATUS = 0
begin

    set @sql = 'select @out = max([' + @col + ']) from [' + @table + ']'
    exec sp_executesql @sql, N'@out int OUTPUT', @out=@current OUTPUT
    update @tables set currentMax = @current where name = @table

    -- skip updating tables that already have a higher id
    if @current >= @baseId
    begin
        update @tables set newSeed = null where name = @table
    end

    fetch next from tableList into @table, @col, @newSeed
end
close tableList
deallocate tableList

-- see plan of action
-- puts tables that are already higher than the new seed first
-- and then tables that have no identity column
-- select * from @tables order by currentMax desc;

-- apply new value

if exists ( select 1 from @tables where newSeed is null and not currentMax is null )
begin
    print ''
    print 'Warning: the following tables already exceed the requested new identity seed:'
    print ''
    select name, identCol, currentMax from @tables where newSeed is null order by currentMax desc;
end

-- get current max id, decide whether to change identity seed
declare targetList cursor for
    select name, identCol, newSeed from @tables where not identCol is null
open targetList
fetch next from targetList into @table, @col, @newSeed
while @@FETCH_STATUS = 0
begin
    if @newSeed is null
    begin
        fetch next from targetList into @table, @col, @newSeed
        continue
    end

    --print 'Setting current identity to ' + cast(@baseId as varchar) + ' for table [' + @table + ']';
    dbcc checkident (@table, reseed, @baseId) WITH NO_INFOMSGS;
    --print '';

    fetch next from targetList into @table, @col, @newSeed
end
close targetList
deallocate targetList
commit