I'm setting up a stored procedure to handle the reseeding "bug" present in SQL Server 2012. Note that as a business we have opted not to change the trace flag setting, as only a couple databases on this instance have applications that rely on sequential identity values and we'd rather not make a global change for just those DBs. I've been informed that it is too time consuming to ask developers to change their code and use programmed id values rather than relying on system generated ones, but I digress.
What I'm attempting to do is get a list of all tables and their respective columns that use Identity and the maximum identity value currently in that column, then, running through a loop, use
DBCC CHECKIDENT to re-seed the table with the next highest value. The procedure will be set to run at instance startup after a failover or power failure.
At the end of all this I'd expect that the max_identity_value column would be updated, but they all remain null. In fact the @sql variable is null at the point where it is being executed. Can anybody tell me what I'm doing wrong?
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET NOCOUNT ON GO declare @update_tracker int = 1, -- identify which row is being updated in the loop @total_rows int = 1, -- total rows in identity columns table @max_identity_id bigint = 0, @table_name nvarchar(max), -- table that has a column with IsIdentity=1 @column_name nvarchar(max) -- column that has IsIdentity=1 declare @sql nvarchar(500) set @sql = 'select max(' + @column_name + ') from [' + @table_name + ']' DECLARE @IdentityColumns table ( column_identifier int identity, -- Oh the irony of using identity in a script that fixes a "bug" in the identity columns of other tables table_name varchar(max), column_name varchar(max), max_identity_value bigint, is_identity bit ) insert into @IdentityColumns select I.table_name, I.column_name, NULL, COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') from INFORMATION_SCHEMA.COLUMNS I where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 set @total_rows = (select max(column_identifier) from @IdentityColumns) set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker) set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker) --select @sql while @update_tracker <= @total_rows begin exec (@sql) set @update_tracker = @update_tracker + 1 set @table_name = (select table_name from @IdentityColumns where column_identifier = @update_tracker) set @column_name = (select column_name from @IdentityColumns where column_identifier = @update_tracker) end select * from @IdentityColumns