Skip to content

Instantly share code, notes, and snippets.

@chrisgilbert

chrisgilbert/reset-identity-columns.sql

Last active July 29, 2020 14:30
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save chrisgilbert/c3e3a19bde3379bb8722cad6bb504269 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save chrisgilbert/c3e3a19bde3379bb8722cad6bb504269 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. chrisgilbert revised this gist Jul 29, 2020. 1 changed file with 10 additions and 11 deletions.
    21 changes: 10 additions & 11 deletions reset-identity-columns.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    declare @column_name varchar(MAX);
    declare @table_name varchar(MAX);
    declare curs CURSOR FOR
    DECLARE @column_name varchar(MAX);
    DECLARE @table_name varchar(MAX);
    DECLARE curs CURSOR FOR
    SELECT
    identity_columns.name as ColumnName,
    tables.name as TableName
    @@ -14,18 +14,17 @@ FETCH curs into @column_name, @table_name
    WHILE @@FETCH_STATUS = 0
    BEGIN

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

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


    SET @SQLString2 = N'select ''DBCC CHECKIDENT(''''' + @table_name + ''''', RESEED, ' + cast(@newseed + @gap as varchar(max)) +')''';
    PRINT 'Executing ' + @SQLString2
    EXECUTE sp_executesql @SQLString2;
    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
  2. chrisgilbert created this gist Jul 29, 2020.
    33 changes: 33 additions & 0 deletions reset-identity-columns.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,33 @@
    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 @SQLString1 NVARCHAR(MAX);
    DECLARE @SQLString2 NVARCHAR(MAX);
    DECLARE @newseed bigint;
    DECLARE @gap bigint = 10000;

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


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

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