Tables metada browser

 Following my previous article I add a simple form to browse tables metada, like, fields, relations, their attributes, etc.

It has the old good horizontal and vertical splitters to facilitate your browsing.

Filtering by attributes like System or Data entity lets rapidly find required subset of tables and fields.

Do not forget that each grid can be exported to Excel.

All data are saved in three InMemory temporary tables, and all the logic is implemented in one class wzhTableTools.

So, you can easily elaborate this solution if some other metada need to be exposed.

Challenge: I still cannot find how to get OnDelete action type for a relation. :(

Class

internal final class wzhTableTools
{
    public static wzhTablesTmp populateTables()
    {
        Dictionary      dict        = new Dictionary();
        int             numOfTables = dict.tableCnt();
        DictTable       dictTable;
        TableId         tableId;
        int             j;
        wzhTablesTmp    wzhTablesTmp;

        // Loop through all tables in the system
        for (j=1 ; j<= numOfTables; j++)
        {
            tableId                     = dict.tableCnt2Id(j);
            dictTable                   = dict.tableObject(tableId);

            wzhTablesTmp.clear();
            wzhTablesTmp.Id             = tableId;
            wzhTablesTmp.Name           = dictTable.name();
            wzhTablesTmp.IsTmp          = dictTable.isTmp();
            wzhTablesTmp.IsView         = dictTable.isView();
            wzhTablesTmp.PName          = dictTable.label();
            wzhTablesTmp.IsAbstract     = dictTable.isAbstract();
            wzhTablesTmp.IsDataEntity   = dictTable.isDataEntity();
            wzhTablesTmp.IsMap          = dictTable.isMap();
            wzhTablesTmp.IsTempDb       = dictTable.isTempDb();
            wzhTablesTmp.IsSystem       = dictTable.isSystemTable();
            wzhTablesTmp.IsSQL          = dictTable.isSql();

            wzhTablesTmp.insert();
        }

        return wzhTablesTmp;
    }

    public static wzhFieldsTmp populateFields(TableId _tableId)
    {
        Dictionary      dict        = new Dictionary();
        DictTable       dictTable   = dict.tableObject(_tableId);
        int             numOfFields = dictTable.fieldCnt();
        int             j;
        FieldId         fieldId;
        DictField       dictField;
        wzhFieldsTmp    wzhFieldsTmp;

        // Loop through all fields in the table
        for (j=1 ; j<= numOfFields; j++)
        {
            fieldId                 = dictTable.fieldCnt2Id(j);
            dictField               = dictTable.fieldObject(fieldId);

            wzhFieldsTmp.clear();
            wzhFieldsTmp.Id                     = fieldId;
            wzhFieldsTmp.Name                   = dictField.name();
            wzhFieldsTmp.Type                   = dictField.baseType();
            wzhFieldsTmp.EDT                    = extendedTypeId2name(dictField.typeId());
            wzhFieldsTmp.IsVisible              = dictField.visible();
            wzhFieldsTmp.IsMandatory            = dictField.mandatory();
            wzhFieldsTmp.IsAllowEdit            = dictField.allowEdit();
            wzhFieldsTmp.IsAllowEditOnCreate    = dictField.allowEditOnCreate();
            wzhFieldsTmp.EDTPName               = extendedTypeId2Pname(dictField.typeId());
            wzhFieldsTmp.PName                  = dictField.label();
            wzhFieldsTmp.IsSystem               = dictField.isSystem();
            wzhFieldsTmp.IsSQL                  = dictField.isSql();
            wzhFieldsTmp.insert();
        }
        return wzhFieldsTmp;
    }

    public static wzhRelatedTablesTmp populateRelatedTables(TableId _tableId, FieldId _fieldId)
    {
        Dictionary      dict        = new Dictionary();
        int             numOfTables = dict.tableCnt();
        DictTable       dictTable;
        DictRelation    dictRelation;
        TableId         tableId;
        int             i, j;
        int             linesCnt;
        int             relationLine;
        int             c;
        str             relName;
        wzhRelatedTablesTmp wzhRelatedTablesTmp;
        
        // Loop through all tables in the system
        for (j=1 ; j<= numOfTables; j++)
        {
            tableId         = dict.tableCnt2Id(j);
            dictTable       = dict.tableObject(tableId);
            dictRelation    = new DictRelation(tableId);
            
            // Loop through all table relations
            int relCount    = dictTable.relationCnt();

            for (i = 1; i <= relCount; i++)
            {
                relName = dictTable.relation(i);
                dictRelation.loadNameRelation(relName);
                
                if (dictRelation && dictRelation.externTable() == _tableId)
                {
                    linesCnt = dictRelation.lines();
                    for (relationLine=1; relationLine <= linesCnt; relationLine++)
                    {
                        // Check if the relation is with AMDeviceTable.DeviceId
                        if (dictRelation.lineExternTableValue(relationLine) == _fieldId)
                        {
                            c++;
                            wzhRelatedTablesTmp.clear();
                            wzhRelatedTablesTmp.Id                         = c;
                            wzhRelatedTablesTmp.FieldId                    = dictRelation.lineTableValue(relationLine);
                            wzhRelatedTablesTmp.RelatedTableName           = dictTable.name();
                            wzhRelatedTablesTmp.RelatedTablePName          = dictTable.label();
                            wzhRelatedTablesTmp.RelationName               = relName;
                            wzhRelatedTablesTmp.Role                       = dictRelation.Role();
                            wzhRelatedTablesTmp.RelatedTableRole           = dictRelation.RelatedTableRole();
                            wzhRelatedTablesTmp.RelatedTableCardinality    = dictRelation.RelatedTableCardinality();
                            wzhRelatedTablesTmp.IsEDTRelation              = dictRelation.EDTRelation();
                            wzhRelatedTablesTmp.Cardinality                = dictRelation.Cardinality();
                            wzhRelatedTablesTmp.RelationshipType           = dictRelation.relationshipType();
                            wzhRelatedTablesTmp.RelatedFieldName           = fieldId2Name(tableId, wzhRelatedTablesTmp.FieldId);
                            wzhRelatedTablesTmp.insert();
                        }
                    }
                }
            }
        }
        return wzhRelatedTablesTmp;
    }

}

Form

[Form]
public class wzhTables extends FormRun
{
    public void clearFields()
    {
        delete_from wzhFieldsTmp;
        delete_from wzhRelatedTablesTmp;
        wzhFieldsTmp_ds.research();
        wzhRelatedTablesTmp_ds.research();
    }

    public void clearRelatedTables()
    {
        delete_from wzhRelatedTablesTmp;
        wzhRelatedTablesTmp_ds.research();
    }

    public void populateTables()
    {
        wzhTablesTmp.setTmpData(wzhTableTools::populateTables());
        wzhTablesTmp_ds.research();
    }

    public void populateFields()
    {
        wzhFieldsTmp.setTmpData(wzhTableTools::populateFields(wzhTablesTmp.Id));
        wzhFieldsTmp_ds.research();
    }

    public void populateRelatedTables()
    {
        wzhRelatedTablesTmp.setTmpData(wzhTableTools::populateRelatedTables(wzhTablesTmp.Id, wzhFieldsTmp.Id));
        wzhRelatedTablesTmp_ds.research();
    }

    [DataSource]
    class wzhTablesTmp
    {
        public int active()
        {
            int ret;
    
            ret = super();
            element.clearFields();
            element.populateFields();
    
            return ret;
        }

        public void init()
        {
            super();
            element.populateTables();
        }

    }

    [DataSource]
    class wzhFieldsTmp
    {
        public int active()
        {
            int ret;
    
            ret = super();
            element.clearRelatedTables();
            element.populateRelatedTables();
    
            return ret;
        }

    }

}