Script to recreate SqlDictionary records in AX 2009

Here's a script that can recreate SqlDictionary records in AX 2009 for any field of (nonsystem) table - in case such record is missing for some reason while the field exists in the database. I used this script during an upgrade to AX 2009 last year. Some fields in the standard application got the same IDs as fields added during customizations and they had different base types, of course, and it seemed impossible to fix the situation using standard means. The script might be of interest to those who wants to know the relations between the SqlDictionary and the AOT.

#macrolib.DictField
// This type is used instead of Types::Int64 for fields of type
// RecId/RefRecId/createdTransactionId/modifiedTransactionId
#define.RecIdBaseType   (49)
// For nonsystem fields of type UtcDateTime an additional field is created
// that holds the actual time zone in which the value has been set
#define.TZIDsuffix      ('_TZID')          

SqlDictionary   sqlDict;
SysdictType     dictType;
DictTable       dictTable;
DictField       dictField;
ArrayIdx        arrIdx;
Counter         numOfSqlFields; // number of records created in SqlDictionary
fieldName       fieldName;
fieldId         fieldId;
tableId         tableId = tablenum(TheTable2Fix);  // TARGET

boolean processTableField(
    DictField   _dictField,
    ArrayIdx    _arrIdx,
    boolean     _isTzIdField = false
    )
{
    ArrayIdx    dictArrIdx;
    str         infoName;       // this field name is for messages only
    FieldName   sqlName;
    boolean     ret;
    ;
    if (_isTzIdField)
    {
        if (    _dictField.baseType()   != Types::UtcDateTime
            ||  _dictField.id()         == fieldnum(Common, createdDateTime)
            ||  _dictField.id()         == fieldnum(Common, modifiedDateTime)
           )
        {
            throw error(Error::wrongUseOfFunction(funcname()));
        }
        dictArrIdx  = _dictField.arraySize() + _arrIdx;
        sqlName     = _dictField.dateTimeTimeZoneRuleFieldName(_arrIdx - 1);
        infoName    = _dictField.name() + #TZIDsuffix;
    }
    else
    {
        dictArrIdx  = _arrIdx;
        sqlName     = _dictField.name(DbBackend::Sql, _arrIdx);
        infoName    = _dictField.name();
    }
    select firstonly sqlDict
        where   sqlDict.tabId   == _dictField.tableid()
            &&  sqlDict.fieldId == _dictField.id()
            &&  sqlDict.array   == dictArrIdx
                ;
    if (!sqlDict)
    {
        sqlDict.clear();
        sqlDict.initValue();
        sqlDict.tabId           = _dictField.tableid();
        sqlDict.fieldId         = _dictField.id();
        sqlDict.array           = dictArrIdx;
        sqlDict.name            = strupr(_dictField.name(DbBackend::Native, _arrIdx));
        sqlDict.sqlName         = sqlName;
        dictType                = new SysDictType(_dictField.typeId());
        if (_isTzIdField)
        {
            sqlDict.fieldType   = Types::Integer;
        }
        else
        if (        _dictField.id()     == fieldnum(Common, RecId)
            ||      _dictField.id()     == fieldnum(Common, createdTransactionId)
            ||      _dictField.id()     == fieldnum(Common, modifiedTransactionId)
            ||      _dictField.typeId() == extendedtypenum(RecId)
            ||      _dictField.typeId() == extendedtypenum(RefRecId)
            ||  (   dictType
                &&  dictType.isExtending(extendedtypenum(RecId))
                )
           )
        {
            // This type is used instead of Types::Int64 for fields of type
            // RecId/RefRecId/createdTransactionId/modifiedTransactionId
            sqlDict.fieldType   = #RecIdBaseType;
        }
        else
        {
            sqlDict.fieldType   = _dictField.baseType();
        }
        sqlDict.strSize         = _dictField.stringLen();
        sqlDict.shadow          = bitTest(_dictField.flags(), #DBF_SHADOW);
        sqlDict.rightJustify    = bitTest(_dictField.flags(), #DBF_RIGHT);
        sqlDict.flags           = sqlDict.shadow;   // not _dictField.flags() at all!
        sqlDict.nullable        =   _dictField.baseType() == Types::Container
                                ||  _dictField.baseType() == Types::VarString
                                    ;
        if (sqlDict.validateWrite())
        {
            sqlDict.insert();
            ret = true;
            info(strfmt(@"Created record for field %1.%2%3",
                        dictTable.name(), infoName,
                        _dictField.arraySize() > 1 ? strfmt(@"[%1]", _arrIdx) : ''));
            // for all nonsystem UtcDateTime fields we also create a related TZID-field
            if (   !_isTzIdField
                &&  _dictField.baseType()   == Types::UtcDateTime
                &&  _dictField.id()         != fieldnum(Common, createdDateTime)
                &&  _dictField.id()         != fieldnum(Common, modifiedDateTime)
               )
            {
                processTableField(_dictField, _arrIdx, true);
            }
        }
        else
        {
            ret = checkFailed(strfmt(@"%1 record for %2.%3 was not created",
                                     tablestr(SqlDictionary), dictTable.name(), infoName));
        }
    }
    return ret;
}
;
dictTable = new DictTable(tableId);
if (!dictTable)
{
    throw error(strfmt(@"Failed to create %1 for '%2' (%3)",
                classstr(DictTable), tableid2name(tableId), tableId));
}
if (dictTable.isSystemTable())
{
    throw error(strfmt(@"'%1' is a system table, no way...", dictTable.name()));
}
if (!dictTable.isSql())
{
    throw error(strfmt(@"Table '%1' should not be in DB", dictTable.name()));
}
for (fieldId = dictTable.fieldNext(0); fieldId; fieldId = dictTable.fieldNext(fieldId))
{
    dictField = dictTable.fieldObject(fieldId);
    if (dictField && dictField.isSql())
    {
        fieldName = dictField.name();
        for (arrIdx = 1; arrIdx <= dictField.arraySize(); arrIdx++)
        {
            numOfSqlFields++;
            processTableField(dictField, arrIdx);
        }
    }
}
select firstonly sqlDict
    where   sqlDict.tabId   == tableId
        &&  sqlDict.fieldId == 0
            ;
if (!sqlDict)
{
    sqlDict.clear();
    sqlDict.initValue();
    sqlDict.tabId       = tableId;
    sqlDict.name        = strupr(dictTable.name());
    sqlDict.sqlName     = dictTable.name(DbBackend::Sql);
    sqlDict.strSize     = numOfSqlFields;       // for the table "header" - num of fields 
    sqlDict.flags       = dictTable.isView();   // that's the way it is
    sqlDict.insert();
    info(strfmt(@"Created record for table %1", dictTable.name()));
}