Query the AOT

I would like to know if there is a way to retreave all table name that match a specific name range.

Lets say we do it by a method showTableNames(‘PBA*’) and the method will output all the tables starting with PBA

Martin could you help me?

UtilElements table will have the required information.

A sample query

while select utilElements

where utilElements.recordType == UtilElementType::Table &&

utilElements.name like ‘del*’

{

info(utilElements.name);

}

Yes you can do this. When you go to project node in tool bar you will find AdvanceFilter/Sort(Ctrl+F3). Then under project filter grouping, Click on AOT and then Select. Under this form you can provide record type as "Table"And Name as “PBA*”.

Kranthi’s approach is very good in AX2009 and older, but it would be too slow in AX2012. Use SysModelElement table instead in AX2012.

I just try Kranthi’s approach and work well. But im curious about your SysModelElement.

Could you give me a code sample, wrapped in a job

Thank you Martin

It’s very similar to UtilElements (UtilElements in AX2012 is just a view to model-related tables), I’m sure you can try it by yourself. Feel free to ask if you ran into any problems, though.

//Kranthi Solution 2000 milliseconds

static void JobutilElements(Args _args)

{

utilElements utilElements;

SysModelElement element;

SysModelElementType elementType;

System.Collections.ArrayList tableNames = new System.Collections.ArrayList();

System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();

int64 elapsed;

;

stopWatch.Start();

while select utilElements

where utilElements.recordType == UtilElementType::Table

// && utilElements.name like ‘del*’

{

// info(utilElements.name);

}

stopWatch.Stop();

// Get the time it took

elapsed = stopWatch.get_ElapsedMilliseconds();

info(strFmt(“Time taken: %1”, elapsed));

}


//Martin Dráb solution 171 milliseconds

static void FetchTableNames_Martin(Args _args)

{

SysModelElement element;

SysModelElementType elementType;

System.Collections.ArrayList tableNames = new System.Collections.ArrayList();

System.Diagnostics.Stopwatch stopWatch = new System.Diagnostics.Stopwatch();

int64 elapsed;

;

stopWatch.Start();

// The SysModelElementType table contains the element types and we need the recId for the next selection

select firstonly RecId

from elementType

where elementType.Name == ‘Table’;

// With the recId of the table element type, select all of the elements with that type (hence, select all of the tables)

while select Name

from element

where element.ElementType == elementType.RecId

// && element.Name like ‘pba*’

// && element.Name like ‘del*’

{

// tableNames.Add(element.Name);

// info(element.Name);

}

stopWatch.Stop();

// Get the time it took

elapsed = stopWatch.get_ElapsedMilliseconds();

info(strFmt(“Time taken: %1”, elapsed));

}


Faster Martin, but for the elegance of the code I prefer Kranthi solution

Thanks guys

------------------------------------------------------------- Look like a double post ---------------------------------------------------------------

You can simplify the code because RecId values in SysModelElementType table corresponds to UtilElementType enum values (the difference is that SysModelElementType has some additional values - see my blog post Model element types in AX2012 for few more details).

You can see that the code is virtually identical to the one using UtilElements table:

SysModelElement element;

while select Name from element
    where element.ElementType == UtilElementType::Table
    && element.Name like 'pba*'
{
    …
}

132 milliseconds … looks like faster and elegant!

Thanks

I can’t get only tables with this code, it return me Maps too. I’m I missing something?


utilElements utilElements;

;

while select utilElements

where 1==1

&& utilElements.recordType == UtilElementType::Table

//&& utilElements.recordType == UtilElementType::TableMap

&& utilElements.name like ‘pba*’

{

info (utilElements.name);

}

table and Map will have the same recordType

You can try this code

UtilIdElements utilIdElements;

DictTable dictTable;

while select Name, Id from utilIdElements where

utilIdElements.RecordType == UtilElementType::Table &&

utilIdElements.name like ‘pba*’

{

dictTable = new DictTable(utilIdElements.Id);

if (!dictTable.isMap())

{

info(utilIdElements.Name);

}

}

Great! but should I worry about other types than map showing up like enum type or extended datatype or else?

I tried to find a dictable.isTable for a noworry solution but dont exist :frowning:

Thanks

views are other object types which will have the recordType as table. So that should be the last condition to verified.

if (!dictTable.isMap() && !dictTable.isView())

Maps and views are tables from AX perspective.

Sometimes you can use existing methods such as SysDictTable::getAllSqlTables(), but you probably have to think about your exact requirements and implement your own logic. Maybe you don’t want to include system tables, tables with disabled configuration keys or who knows what…

Need to add a group by because it was returning element twice.

while select Name, Id from utilIdElements

group by Name