SQL Server 2005中删除数据后如何重置表的自增列?

发布网友发布时间：2022-04-08 08:53

共2个回答

懂视网时间：2022-04-08 13:14

@tablename varchar(50) declare @sql varchar(1000) declare cur cursor for select name from sys.tables open cur fetch next from cur into @tablename while @@fetch_status=0 begin set @sql=‘if (select count(1) from ‘+@tablename+‘)<=0 and exists(select * from sys.columns where is_identity=1 and object_id=object_id(‘‘‘+@tablename+‘‘‘)) begin --dbcc checkident(‘+@tablename+‘,reseed,1) dbcc checkident(‘+@tablename+‘,reseed) end‘ exec (@sql) fetch next from cur into @tablename end close cur deallocate cur

测试过没有问题的

--已经测试没有问题的生成有标识列（自增ID）的表名
declare @tablename varchar(50) 
declare @sql varchar(1000)
declare @objiecid int
declare cur cursor for select object_id from sys.columns where is_identity=1 
open cur
fetch next from cur into @objiecid
while @@fetch_status=0
begin
select @tablename=name from sys.tables where object_id=@objiecid
--print @tablename
print ‘dbcc checkident(‘‘‘+@tablename+‘‘‘,reseed)‘
fetch next from cur into @objiecid 
end 
close cur
deallocate cur

利用SQL语句重置数据库中所有表的标识列(自增量)

标签：

热心网友时间：2022-04-08 10:22

使用TRUNCATE
TABLE
[TABLENAME]语句。但是这样就删除了整张表的数据，而不能选择性的删除。但删除效率比DELETE高。