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?


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')
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

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)


select * from @IdentityColumns
  • 1
    It seems unfair to accept the answer given then incorporate the answer into your revised question. On reflection, I think it would be better if you asked a new question. I have rolled back your edit; see the revision history to get the revised text. – Paul White 9 Jan 9 '16 at 1:24

Your set @sql statement includes 2 null variables so @sql is null.

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.