602SQL Documentation Index  

System Tables

Each database has several system tables, that contain object definitions, table definitions, user list and another important items.

It's not recommended to work with these tables directly. You can use the interactive developer interface or the appropriate API functions or SQL statements for creating, altering and deleting objects instead. The system tables' structure may change in future versions of 602SQL.

The system tables are:

OBJTAB table of objects of all applications (queries, procedures, roles etc.) - accessible for reading and writing
TABTAB table of tables of all applications - only for reading
USERTAB table of users and groups - only for reading

An overview of some columns in the system tables:

Column OBJTAB TABTAB USERTAB Type Meaning
OBJ_NAME yes -- -- CHAR(31) object name
TAB_NAME -- yes -- CHAR(31) table name
LOGNAME -- -- yes CHAR(31) user name or group name
CATEGORY yes yes yes CHAR object category
APL_UUID yes yes -- BINARY(12) application identification
USR_UUID -- -- yes BINARY(12) user (group) identification
DEFIN yes yes -- BLOB text definition of an object
FOLDER_NAME yes yes -- CHAR(31) folder name on the control panel
LAST_MODIF yes yes -- TIMESTAMP time and date of creation or last change

Example: get the names of database users

Solution: ask a query

SELECT logname
FROM Usertab
WHERE (Usertab.category=Chr(CATEG_USER))

Example: select all objects from the "APPLICATION1"

Solution: ask these two queries

SELECT T2.obj_name,Ord(T2.category)
FROM Objtab T1, Objtab T2
WHERE T1.apl_uuid=T2.apl_uuid
 AND (T1.obj_name="APPLICATION1" AND Ord(T1.category)=CATEG_APPL)

and

SELECT Tabtab.tab_name
FROM Objtab, Tabtab
WHERE Objtab. apl_uuid =Tabtab. apl_uuid
 AND Objtab.obj_name="APPLICATION1"

Object Categories Constants

These constants identify the object categories in 602SQL. All of them are Integer type:

application objects category  
CATEG_TABLEtable 0
CATEG_CURSORquery3
CATEG_PGMSRCdata transfer description4
CATEG_APPLapplication7
CATEG_ROLE application role10
CATEG_DRAWING diagram13
CATEG_PROCstored procedure16
CATEG_TRIGGERtrigger17
CATEG_FOLDERfolder19
CATEG_SEQsequence20
CATEG_INFOfulltext21
CATEG_DOMAINdomain22
CATEG_STSHstylesheet23
CATEG_XMLFORMXML form24
database objects category  
CATEG_USERuser1
CATEG_GROUPusergroup9

BEWARE! The CATEGORY column in the system tables is Char type, therefore don't forget to make the appropriate conversion by the Ord or Chr functions when comparing.