Declare @IdentityTable sysname,
@IdentityColumn sysname,
@TotalRows int,
@i int,
@Iden int,
@Sql varchar (4000)
SET NOCOUNT on
--Identity List
Create Table #IDENTITY_Table
(
Seqid_int int identity (+),
identity_table sysname NULL,
Identity_column sysname NULL,
Identity_max int NULL default (0)
)
Create Table #IDENTITY_Table_Column_Value
(
Identity_max int
)
Insert into #IDENTITY_Table (identity_table,identity_column)
Select--' Select Ident_current (' ' + obj.name + ') ',
Obj.name,
Col.name
From sys.tables as OBJ. Outer join Sys.columns as Col on obj.object_id = col.object_id
and col.is_identity = 1
Where obj.type = ' U '
and OBJECTPROPERTY (object_id (obj.name), ' tablehasidentity ') = 1;
Set @TotalRows = @ @ROWCOUNT;
Set @i = 1;
--loop to get each identity list and update to the current maximum identity column value
while (@i <= @TotalRows)
Begin
Select @IdentityTable = identity_table,
@IdentityColumn = Identity_column
From #IDENTITY_Table
Where seqid_int = @i;
Set @Sql = ' Insert into #IDENTITY_Table_Column_Value ' + char (Ten) + ' Select max (' + @IdentityColumn + ') + 1 from ' + @Iden titytable;
--print @Sql
EXEC (@Sql);
Select @Iden = Identity_max from #IDENTITY_Table_Column_Value;
Set @Iden = IsNull (@Iden, 1)
Update #IDENTITY_Table
Set Identity_max = @Iden
where identity_table = @IdentityTable;
--DBCC checkident (@IdentityTable, reseed, @Iden)
--print @IdentityTable + ', ' + cast (@Iden as varchar (10))
Set @i = @i + 1;
Set @Iden = 0
Set @Sql = ' '
TRUNCATE TABLE #IDENTITY_Table_Column_Value
End
/*
Truncate Table #IDENTITY_Table;
Drop Table #IDENTITY_Table;
TRUNCATE TABLE #IDENTITY_Table_Column_Value
drop table #IDENTITY_Table_Column_Value
*/
SET NOCOUNT OFF
-----------------------------------------------
--select ' DBCC checkident (' + identity_table + ', reseed, ' + cast (identity_max as varchar) + ') ' from #IDENTITY_Table
SELECT * FROM #IDENTITY_Table
SQL Reset self-increment column value batch processing