Tech Tip: Saving Field and Table Names to a Two-Dimensional Array

PRODUCT: 4D | VERSION: 11.3 | PLATFORM: Mac & Win

Published On: January 28, 2009

There are two easy ways to obtain the names of all tables and fields. One is by using 4D language as shown in the language reference online. The other is using SQL to query the system tables in 4D.

For example, let say that our database has the following structure:

The following code uses SQL's EXECUTE IMMEDIATE to get the table and field names from the given structure.

tableNumber:=Get last table number
ARRAY TEXT(fieldNames_2a;tableNumber;0)

for($index;1;tableNumber)
    if( is table number valid(tableNumber))
       $stmnt:="select COLUMN_NAME from _USER_COLUMNS where TABLE_ID="+string($index)
       $stmnt:=$stmnt+"into :fieldName_a"
       BEGIN SQL
         EXECUTE IMMEDIATE :$stmnt;
       END SQL
           for($columnCounter;1;size of array(fieldName_a)
               if(is field number valid($index;$columnCounter))
                  INSERT IN ARRAY(fieldNames_2a{$index};$columnCounter;1)
                  fieldNames_2a{$index};{$columnCounter}:=Field name($index;$columnCounter)
               end if
           end for
   end if
end for

We have all tables and fields stored in a two dimensional array.

Commented by on May 19, 2009 at 7:15 AM

This snippet of code assumes that you have never deleted fields. $columncounter only increments as high as the size of the array of field names. If fields have been deleted, then the field numbers will be larger than the size of the array.