Declare @t table(Id INT identity(1,1), tName nvarchar(max), cName nvarchar(max))
Declare @t1 table(ID INT,tName nvarchar(max), cCount BIGINT)
Declare @SQry nVarchar(MAX) = '', @tName nvarchar(max), @cName nvarchar(max)
Declare @nCount int,@sCount int=1
Insert into @t(tName,cName)
select a.name,b.name from sys.tables a inner join sys.all_columns b on a.object_id = b.object_id
where b.is_identity = 1
select @nCount = COUNT(*) from @t
While (@sCount <= @nCount)
BEGIN
select @tName = tname,@cName = cName from @t where id = @sCount
--print N'select max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_CURRENT( '''+ @tName +''') <> max('+ @cName +')'
Insert into @t1(ID,cCount, tName)
exec (N'select '+ @sCount +', max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_CURRENT( '''+ @tName +''') <> max('+ @cName +')');
select @SQry = 'DBCC CHECKIDENT(''' + Tname + ''', RESEED, ' + Cast(cCount as varchar(50)) + ')' from @t1 where ID = @sCount
print @SQry
EXEC (@SQry)
set @sCount = @sCount + 1
END
Declare @t1 table(ID INT,tName nvarchar(max), cCount BIGINT)
Declare @SQry nVarchar(MAX) = '', @tName nvarchar(max), @cName nvarchar(max)
Declare @nCount int,@sCount int=1
Insert into @t(tName,cName)
select a.name,b.name from sys.tables a inner join sys.all_columns b on a.object_id = b.object_id
where b.is_identity = 1
select @nCount = COUNT(*) from @t
While (@sCount <= @nCount)
BEGIN
select @tName = tname,@cName = cName from @t where id = @sCount
--print N'select max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_CURRENT( '''+ @tName +''') <> max('+ @cName +')'
Insert into @t1(ID,cCount, tName)
exec (N'select '+ @sCount +', max('+ @cName +') nCount, '''+ @tName +''' Tname from '+ @tName +' having IDENT_CURRENT( '''+ @tName +''') <> max('+ @cName +')');
select @SQry = 'DBCC CHECKIDENT(''' + Tname + ''', RESEED, ' + Cast(cCount as varchar(50)) + ')' from @t1 where ID = @sCount
print @SQry
EXEC (@SQry)
set @sCount = @sCount + 1
END
Comments
Post a Comment