print '** BEGIN **' declare @TABID int, @nextval bigint, @lastUsed bigint, @sql NVARCHAR(4000),
@msgName varchar(250), @tablename varchar(250)
DECLARE loopTrough CURSOR FOR
select TABID, NEXTVAL from systemsequences where systemsequences.Name =
'SEQNO' and
systemsequences.DATAAREAID = 'dat' and
systemsequences.TABID > 0
open loopTrough
FETCH next FROM loopTrough INTO @TABID, @nextval
WHILE @@FETCH_STATUS = 0
BEGIN
set @tablename = isnull((select [SQLNAME] from SQLDICTIONARY where
SQLDICTIONARY.FIELDID = 0 and SQLDICTIONARY.TableId = @TABID), '')
if @tablename <> ''
set @msgName = @tablename + '(No. ' + convert(varchar(20), @TABID) + ')'
else
set @msgName = '(No. ' + convert(varchar(20), @TABID) + ')'
print 'Analyze ' + @msgName + ' ...'
IF @tablename <> '' and
EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME=@tablename)
begin
set @sql = N'SELECT @ret = MAX(RECID) FROM ' + @tablename
EXEC sp_executesql @sql, N'@ret bigint OUTPUT', @lastUsed OUTPUT
if (@nextVal < @lastUsed)
begin
print '> have to be corrected (next RecId: ' + convert(varchar(250),
@nextVal) + ' < max RecID: ' + convert(varchar(250), @lastUsed) + ')'
update systemsequences set nextval = (@lastUsed + 1) where
systemsequences.Name = 'SEQNO' and
systemsequences.TABID = @TABID and
systemsequences.DATAAREAID = 'dat'
print '> corrected; next RecId: ' + convert(varchar(250), @lastUsed + 1)
end
end
else
begin
print @msgName + ' not in SQLDictionary or Table not in DataBase'
end
FETCH next FROM loopTrough INTO @TABID, @nextval
END
close loopTrough
DEALLOCATE loopTrough
print '** Finish **'