SQL2008修改标识列

----------------------------------------------------------------------------------------
---SQL2008
----------------------------------------------------------------------------------------


Declare		@IdentityTable		sysname,
		@IdentityColumn		sysname,
		@TotalRows		int,
		@i			int,
		@Iden			bigint,
		@Sql			varchar(4000)
		

SET NOCOUNT ON
--标识列表
Create Table #IDENTITY_Table
(
	SeqID_int 			int  identity(1,1), 
	IDENTITY_TABLE	 		sysname null,
	IDENTITY_COLUMN			sysname null,
	IDENTITY_MAX			bigint null default(0)
)

Create Table #IDENTITY_Table_Column_Value
(
	IDENTITY_MAX			bigint
)

Insert Into #IDENTITY_Table(IDENTITY_TABLE,IDENTITY_COLUMN)
	Select --'select IDENT_CURRENT(''' + Obj.name + ''')',
		Obj.name,
		Col.name 
	From sys.tables AS Obj left 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;

--循环得到每一个标识列表,然后更新为当前最大标识列值
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(10) + 'select max(' + @IdentityColumn + ') + 50 from ' + @IdentityTable;
	--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(20)) + ')' from #IDENTITY_Table

 

------------------------------------------------------------------
SQL2000
------------------------------------------------------------------

Declare		@IdentityTable		sysname,
		@IdentityColumn		sysname,
		@TotalRows		int,
		@i			int
		

SET NOCOUNT ON
--标识列表
Create Table #IDENTITY_Table
(
	SeqID_int 			int  identity(1,1), 
	IDENTITY_TABLE	 		sysname,
	IDENTITY_COLUMN			sysname,
	IDENTITY_MAX			int
)

Insert Into #IDENTITY_Table(IDENTITY_TABLE,IDENTITY_COLUMN)
	Select Obj.name,Col.name 
	From sysobjects AS Obj left outer join syscolumns AS Col on Obj.id = Col.id and Col.autoval is not null 
   	Where Obj.type = 'U' and objectproperty(object_id(Obj.name),'TableHasIdentity') = 1;


Set @TotalRows = @@ROWCOUNT;
Set @i = 1;

--循环得到每一个标识列表,然后更新为当前最大标识列值
WHILE(@i <= @TotalRows)
Begin
	Select 	@IdentityTable  = IDENTITY_TABLE,
		@IdentityColumn = IDENTITY_COLUMN
	From #IDENTITY_Table 
	Where SeqID_int = @i;
		
	DBCC CHECKIDENT( @IdentityTable, RESEED )
	
	Set @i = @i + 1;
End

Truncate Table #IDENTITY_Table;
Drop Table #IDENTITY_Table;

SET NOCOUNT OFF



-----------------------------------------------------

----------------------------------------------------------------------------------------
---SQL2008
----------------------------------------------------------------------------------------
Declare		@IdentityTable		sysname,
		@IdentityColumn		sysname,
		@TotalRows		int,
		@i			int,
		@Iden			int,
		@Sql			varchar(4000)
		

SET NOCOUNT ON
--标识列表
Create Table #IDENTITY_Table
(
	SeqID_int 			int  identity(1,1), 
	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 left 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;

--循环得到每一个标识列表,然后更新为当前最大标识列值
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(10) + 'select max(' + @IdentityColumn + ') + 1 from ' + @IdentityTable;
	--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(10)) + ')' from #IDENTITY_Table