@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语句重置数据库中所有表的标识列(自增量)
标签：
