Re-align table ID’s

While all effort should be made to prevent ID’s in your DAX environment to get out of aligned, if they do then the below script might be of assistance. This script is designed to re-align table ID’s only. It should be run twice. The first run will move tables, if necessary to the end of the table list, then the second run will move then to their correct position. This is to handle the issue of another table already using the ID that should be assigned, so allows the other table to be moved out of the way so the second pass can put everything in the correct place.

static void AlignTableIds(Args _args)
{
Dictionary dictionary = new Dictionary();
SysDictTable dictTable;
TableId tableId;
SqlDictionary sqlDictionaryTable;
SqlDictionary sqlDictionaryTableNew;

int64 icount;
SysOperationProgress op;

boolean bPass = false;
boolean bPassTest;

setPrefix(“Update of data dictionary IDs”);

select count(RecId) from sqlDictionaryTable
where sqlDictionaryTable.fieldId == 0;

tableId = dictionary.tableNext(0);

op = new SysOperationProgress();
op.setTotal(sqlDictionaryTable.RecId);

while (tableId)
{
dictTable = new SysDictTable(tableId);
setPrefix(dictTable.name());

op.incCount();
op.setText(dictTable.name());

if (!dictTable.isSystemTable())
{
//Finds table in SqlDictionary by name in AOT, if ID was changed.
//Empty field ID represents a table.
select sqlDictionaryTable
where sqlDictionaryTable.name == dictTable.name()
&& sqlDictionaryTable.fieldId == 0
&& sqlDictionaryTable.tabId != dictTable.id();

if (sqlDictionaryTable)
{
select sqlDictionaryTableNew
where sqlDictionaryTableNew.fieldId == 0
&& sqlDictionaryTableNew.tabId == sqlDictionaryTable.tabId;

if(sqlDictionaryTableNew.tabId)
{
Select maxOf(tabid) from sqlDictionaryTableNew
where sqlDictionaryTableNew.fieldid == 0;

//Updates table ID in SqlDictionary
if (ReleaseUpdateDB::changeTableId(
dictTable.id(),
sqlDictionaryTableNew.tabId + 1,
sqlDictionaryTable.name))
{
info(strFmt(“Table ID reset (%1 -> %2)”, dictTable.id(), sqlDictionaryTableNew.tabId + 1));
}
}
else{
//Updates table ID in SqlDictionary
if (ReleaseUpdateDB::changeTableId(
dictTable.id(),
sqlDictionaryTable.tabId,
dictTable.name()))
{
info(strFmt(“Table ID changed (%1 -> %2)”, dictTable.id(), sqlDictionaryTable.tabId));
}
}
}
}
tableId = dictionary.tableNext(tableId);
}
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s