Tech Tip: Utility Method to get Table and Column Information

PRODUCT: 4D | VERSION: 16 R5 | PLATFORM: Mac & Win

Published On: February 14, 2018

The following utility method takes no input parameters and returns a collection of objects which contain table and column information from the structure.

*Note: As this utility takes advantage of the C_COLLECTION data type, 4D version 16 R4 or later is required. Object notation must be turned on.

ARRAY TEXT($t_name_at;0)
ARRAY TEXT($t_data_at;0)
ARRAY LONGINT($t_id_al;0)
ARRAY LONGINT($t_schema_al;0)
ARRAY BOOLEAN($t_log_ab;0)
ARRAY BOOLEAN($t_temp_ab;0)
ARRAY BOOLEAN($t_repl_ab;0)
ARRAY BOOLEAN($t_rest_ab;0)
ARRAY TEXT($c_name_at;0)
ARRAY TEXT($c_data_at;0)
ARRAY LONGINT($c_t_id_al;0)
ARRAY LONGINT($c_data_type_al;0)
ARRAY LONGINT($c_data_len_al;0)
ARRAY LONGINT($c_id_al;0)
ARRAY BOOLEAN($c_null_ab;0)
ARRAY BOOLEAN($c_autoinc_ab;0)
ARRAY BOOLEAN($c_unique_ab;0)
ARRAY LONGINT($i_t_id;0)
ARRAY LONGINT($i_col_id;0)
ARRAY LONGINT($i_id;0)
C_TEXT($indexed_col_t)
C_LONGINT($runner_l)
C_OBJECT($c_data_o;$c_name_o;$t_name_o;$t_data_o)
C_COLLECTION($t_col_co)

Begin SQL
   SELECT TABLE_NAME,TABLE_ID,SCHEMA_ID,LOGGED,TEMPORARY,
   REPLICATION,REST_AVAILABLE
   FROM _USER_TABLES
   INTO :$t_name_at,:$t_id_al,:$t_schema_al,:$t_log_ab,
   :$t_temp_ab,:$t_repl_ab,:$t_rest_ab;

   SELECT TABLE_ID,COLUMN_NAME,DATA_TYPE,DATA_LENGTH,
   NULLABLE,COLUMN_ID,AUTOINCREMENT,UNIQUENESS
   FROM _USER_COLUMNS
   INTO :$c_t_id_al,:$c_name_at,:$c_data_type_al,:$c_data_len_al,
   :$c_null_ab,:$c_id_al,:$c_autoinc_ab,:$c_unique_ab;

   SELECT B.TABLE_ID,B.COLUMN_ID,A.INDEX_TYPE
   FROM _USER_INDEXES A, _USER_IND_COLUMNS B
   WHERE A.INDEX_ID = B.INDEX_ID
   INTO :$i_t_id,:$i_col_id,:$i_id;
End SQL

For ($i;1;Size of array($c_name_at))
   $indexed_col_t:="None"
   For ($h;1;Size of array($i_t_id))
      If (($c_id_al{$i}=$i_col_id{$h}) & ($c_t_id_al{$i}=$i_t_id{$h}))
         Case of
            : ($i_id{$h}=1)
            $indexed_col_t:="BTree/Composite"
            : ($i_id{$h}=3)
            $indexed_col_t:="Cluster/Keyword"
          Else
            $indexed_col_t:="Auto"
         End case
      End if
   End for
   $c_data_o:=New object("column_id";$c_id_al{$i};"table_id";$c_t_id_al{$i};"data_type";\
     $c_data_type_al{$i};"data_length";$c_data_len_al{$i};"nullable";$c_null_ab{$i};\
     "autoinc";$c_autoinc_ab{$i};"unique";$c_unique_ab{$i};"index";$indexed_col_t)
   $c_name_o:=New object("column_name";$c_name_at{$i};"column_data";$c_data_o)
   APPEND TO ARRAY($c_data_at;JSON Stringify($c_name_o))
End for

For ($j;1;Size of array($t_name_at))
   $runner_l:=0
   $t_col_co:=New collection
   For ($k;1;Size of array($c_data_at))
      If ($t_id_al{$j}=OB Get(OB Get(JSON Parse($c_data_at{$k});"column_data");"table_id"))
         $t_col_co[$runner_l]:=JSON Parse($c_data_at{$k})
         $runner_l:=$runner_l+1
      End if
   End for
   $t_data_o:=New object("table_id";$t_id_al{$j};"schema";$t_schema_al{$j};"logged";$t_log_ab{$j};"temp";\
     $t_temp_ab{$j};"replication";$t_repl_ab{$j};"rest";$t_rest_ab{$j};"columns";$t_col_co)
   $t_name_o:=New object("table_name";$t_name_at{$j};"table_data";$t_data_o)
   APPEND TO ARRAY($t_data_at;JSON Stringify($t_name_o))
End for

$0:=JSON Parse(JSON Stringify array($t_data_at))

As mentioned, the output of this utility method is a collection of objects, with each object representing one table from the structure. For example, running this method in a database with 5 tables results in an output collection with 5 elements:

Each element of the collection, which represent information for a single table, can then be accessed just like stepping through an array, so to access the "Catalog" table info object from the above example, simply take the output from the function and call the third element:

C_COLLECTION($output_collection)

$output_collection:=Util_Find_Tables

// example display on a form, with tableInfo as a form object
tableInfo:=JSON Parse($output_collection[3])

Sample table info object (from output collection):