Reset SQL Server Identity Columns With a Gap

reset-identity-columns.sql

DECLARE @column_name varchar(MAX);
DECLARE @table_name varchar(MAX);
DECLARE curs CURSOR FOR 
SELECT 
	identity_columns.name as ColumnName,
    tables.name as TableName
FROM sys.tables tables 
	JOIN sys.identity_columns identity_columns 
ON tables.object_id=identity_columns.object_id
ORDER BY tables.name;

OPEN curs
FETCH curs into @column_name, @table_name
WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @getMaxSQL NVARCHAR(MAX);  
    DECLARE @identSQL NVARCHAR(MAX);
    DECLARE @newseed bigint;
    DECLARE @gap bigint = 10000;

    SET @getMaxSQL = 'select @seedOut = max(' + @column_name + ') from ' + @table_name;
    EXECUTE sp_executesql @getMaxSQL, N'@seedOut bigint OUTPUT', @seedOut = @newseed OUTPUT

    SET @identSQL = N'select ''DBCC CHECKIDENT(''''' + @table_name + ''''', RESEED, ' + cast(@newseed + @gap as varchar(max)) +')''';
    PRINT 'Executing ' + @identSQL
    EXECUTE sp_executesql @identSQL;

    FETCH curs into @column_name, @table_name
END
CLOSE curs
DEALLOCATE curs