Reset Identity Columns

/******************************************************************************//*  RESET_IDENTITY_COLUMNS.SQL                                           *//*                                                                            *//*  Reset's the identity seed value to 1 greater than than the max value      *//*  currently in that column, for all tables in XXXX schema.                  *//*                                                                            *//*  HISTORY                                                                   *//*  DATE     INITIALS   COMMENTS                                              *//*  070416   KVA        Initial Creation.                                     *//*                                                                            *//******************************************************************************/
use db_name
go

BEGIN
DECLARE         @lv_table_namevarchar(35),
@lv_column_name varchar(35),
@lv_countint,
@lv_count_strvarchar(15),
        @lv_sql_stmtvarchar(400)

DECLARE cur_tables CURSOR FOR
select o.name, c.name
from sys.columns c, sys.objects o, sys.schemas s
where c.object_id = o.object_id
  and o.schema_id = s.schema_id
  and c.is_identity = 1
  and o.type_desc = 'USER_TABLE'
  and s.name = 'XXXX'
order by o.name

BEGIN

create table ##temp_count (rec_id int, mycount int)
insert into ##temp_count values (1,0)

OPEN cur_tables
FETCH NEXT FROM cur_tables INTO @lv_table_name, @lv_column_name
WHILE @@FETCH_STATUS = 0
BEGIN

select @lv_sql_stmt = 'update ##temp_count set mycount = (select max('+ @lv_column_name +') from prod.' + @lv_table_name + ') where rec_id=1'
execute (@lv_sql_stmt)

select @lv_count = (mycount+1) from ##temp_count where rec_id = 1
select @lv_count_str = STR(@lv_count)

select @lv_sql_stmt = 'DBCC CHECKIDENT ( ''prod.' + @lv_table_name + ''', RESEED, ' + @lv_count_str + ')'
execute (@lv_sql_stmt)

FETCH NEXT FROM cur_tables INTO @lv_table_name, @lv_column_name
END

drop table ##temp_count

CLOSE cur_tables
DEALLOCATE cur_tables

END
END