Re-align Identity Last Value to Actual Max Value (more info: https://eitanblumin.com/2018/11/06/re-align-identity-last-value-to-actual-max-value/ )
/* | |
Author: Eitan Blumin (t: @EitanBlumin | b: eitanblumin.com) | |
Date: November, 2018 | |
Description: | |
Re-align Identity Last Value to Actual Max Value | |
More info: https://eitanblumin.com/2018/11/06/re-align-identity-last-value-to-actual-max-value/ | |
*/ | |
DECLARE @CurrTable SYSNAME, @CurrCol SYSNAME, @LastValue BIGINT | |
DECLARE @CMD NVARCHAR(MAX), @Result NVARCHAR(MAX) | |
DECLARE Cur CURSOR | |
LOCAL FAST_FORWARD | |
FOR | |
SELECT DISTINCT QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + '.' + QUOTENAME(OBJECT_NAME(t.object_id)), c.name, CONVERT(bigint, c.last_value) | |
FROM sys.identity_columns AS c | |
INNER JOIN sys.tables AS t | |
ON c.object_id = t.object_id | |
WHERE c.last_value <> c.seed_value | |
OPEN Cur | |
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @CMD = N' | |
SELECT @pResult = N''DBCC CHECKIDENT(''''' + @CurrTable + N''''', RESEED, '' + CONVERT(nvarchar(max), MAX(' + QUOTENAME(@CurrCol) + N')) + N'') -- ' + CONVERT(nvarchar(max), @LastValue) + N''' | |
FROM ' + @CurrTable + N' | |
HAVING MAX(' + QUOTENAME(@CurrCol) + N') <> @LastValue' | |
EXEC sp_executesql @CMD, N'@pResult NVARCHAR(MAX) OUTPUT, @LastValue BIGINT', @Result OUTPUT, @LastValue; | |
IF @Result IS NOT NULL | |
PRINT @Result; | |
FETCH NEXT FROM Cur INTO @CurrTable, @CurrCol, @LastValue | |
END | |
CLOSE Cur | |
DEALLOCATE Cur |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
TODO:
Implement proper support for a wider variety of column data types.