CREATE PROCEDURE SP_RESEED(@TNAME NVARCHAR(100))
AS
BEGIN
DECLARE @s NVARCHAR(4000),@v NVARCHAR(8000),@cName NVARCHAR(100)
CREATE TABLE #T1(NAME VARCHAR(100))
--去自增字段名
SET @s = N'SELECT @cName = NAME FROM SYSOBJECTS WHERE ID=OBJECT_ID('''+@TNAME+N''') AND AUTOVAL IS NOT NULL'
EXEC SP_EXECUTESQL @s,N'@cName NVARCHAR(100) OUT',@cName OUT
--取其他字段名
SET @s = 'INSERT INTO #T1 SELECT NAME FROM SYSOBJECTS WHERE ID=OBJECT_ID('''+@TNAME+''') AND AUTOVAL IS NULL'
EXEC(@s)
SET @v = ''
SELECT @v = @v + ',' + NAME FROM #T1
SET @v = STUFF(@v,1,1,'')
SET @s = 'SELECT * INTO #T from '+@TNAME+' ORDER BY '+@cName
SET @s = @s + ' TRUNCATE TABLE '+@TNAME
SET @s = @s + ' DBCC CHECKIDENT ('''+@TNAME+''', RESEED, 1)'
SET @s = @s + ' INSERT INTO '+@TNAME+'('+@v+') SELECT '+@v+' FROM #T ORDER BY '+@cName
exec sp_msforeachtable
@command1='if exists(select 1 from syscolumns
where id=object_id(''?'')
and autoval is not null)
begin
declare @name varchar(20)
select @name=name from syscolumns
where id=object_id(''?'') and autoval is not null
exec(''alter table ? drop column ''+@name)
exec(''alter table ? add ''+@name+'' int identity(1,1)'')
end
'
exec sp_msforeachtable @command1='if exists(select 1 from syscolumns
where id=object_id(''?'')
and autoval is not null)
begin
declare @name varchar(20)
select @name=name from syscolumns
where id=object_id(''?'') and autoval is not null
exec(''alter table ? drop column ''+@name)
exec(''alter table ? add ID int identity(1,1)'')
end
'
假定表结构如下:
------------------------------------------------------------------------
create table T(id int identity(1,1),name varchar(20),desc varchar(20))
GO
--将数据倒入临时表
select * into #T from T order by id
--清空源表
truncate table T
GO
--重置标识列
DBCC CHECKIDENT (T, RESEED, 1)
GO
--将数据导回源表
insert into T(name,desc) select name,desc from #T order by id