This SQL loops through all tables in an AX business database (should work for versions 4.0, 2009, and 2012 with little or no modification), using the SQLDICTIONARY table as a mean of translating tableId's found in SYSTEMSEQUENCES into table names (in AX 2012 and beyond this can also be done from the ModelElements table in the model store database). There are tables in AX not found in either SQLDICTIONARY or ModelElements which may need to be considered through other means.
Use this SQL at your own risk. It is provided for academic purposes only.
declare @t INT -- TableId
declare @taot NVARCHAR(40) -- TableName (AOT)
declare @tsql NVARCHAR(40) -- identifierstr (SQL)
declare @cmd NVARCHAR(4000)
declare @maxrecid bigint
declare @nextval bigint
-- loop through all tables references in SQLDICTIONARY with a DATAAREAID field
declare dict insensitive cursor for
select TABLEID, NAME, SQLNAME
from SQLDICTIONARY
where FIELDID = 0
and SQLNAME in (select t.name
from sys.tables t
join sys.syscolumns c on c.id = t.object_id
where c.name = N'RECID')
and SQLNAME not in (N'SYSTEMSEQUENCES',N'SQLSYNCINFO') -- special table
order by TABLEID
open dict
fetch next from dict into @t, @taot, @tsql
while @@FETCH_STATUS = 0
begin
set @cmd = N'select @ret = MAX(RECID) from ' + @tsql
exec sp_executesql @cmd, N'@ret bigint output', @maxrecid output
set @cmd = N'select @ret = NEXTVAL from SYSTEMSEQUENCES where ID=-1 and TABID=' +convert(nvarchar(28), @t)
exec sp_executesql @cmd, N'@ret bigint output', @nextval output
if coalesce(@maxrecid,0) >= @nextval
begin
print N'tableid: ' + convert(nvarchar(28),@t) + N' ' + @tsql + N' recid: ' + convert(nvarchar(28),coalesce(@maxrecid,0)) + N' nextvalue: ' + convert(nvarchar(28),@nextval)
end
fetch next from dict into @t, @taot, @tsql
end
close dict
deallocate dict
