Tech Tip: Code to generate a log of all non-indexed-unique fields

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

Published On: April 14, 2014

The following code snippet generates a report of all unique fields that exist in the database without an index:

C_LONGINT($maxTableNumber_l;$currentTable_l)
C_LONGINT($maxFieldCount_l;$currentField_l)
C_LONGINT($dontCare_l) // For GET FIELD PROPERTIES values that are not used.
C_BOOLEAN($dontCare_f;$isIndexed_f;$isUnique_f)
C_TEXT($logHeader_t;$logRecord_t;$logfile_t)
C_TEXT($delim_t;$lf_t)
C_TIME($logfile_h)
C_TEXT($tableName_t;$fieldName_t;$note_t)

$delim_t:=Char(Tab)
$lf_t:=Char(Carriage return)+Char(Line feed)

$logHeader_t:="Unique fields without index:"+$lf_t

$logfile_t:=Get 4D folder(Logs Folder)+"UniqueNotIndexed.txt"

$logfile_h:=Create document($logfile_t)

If (OK=1)

   SEND PACKET($logfile_h;$logHeader_t)

   $maxTableNumber_l:=Get last table number

   For ($currentTable_l;1;$maxTableNumber_l)
      If (Is table number valid($currentTable_l))
         $maxFieldCount_l:=Get last field number(Table($currentTable_l))
         For ($currentField_l;1;$maxFieldCount_l)
            If (Is field number valid($currentTable_l;$currentField_l))

                // Note the following line breaks over two lines in text,
                // it is one statement in the method:
               GET FIELD PROPERTIES($currentTable_l;$currentField_l;$dontCare_l;\
                   $dontCare_l;$isIndexed_f;$isUnique_f;$dontCare_f)

               If (($isUnique_f) & (Not($isIndexed_f)))

                  $tableName_t:=Table name(Table($currentTable_l))
                  $fieldName_t:=Field name(Field($currentTable_l;$currentField_l))

                  $logRecord_t:="["+$tableName_t+"]"+$fieldName_t+$lf_t

                  SEND PACKET($logfile_h;$logRecord_t)

               End if
            End if
         End for
      End if
   End for

   CLOSE DOCUMENT($logfile_h)
   SHOW ON DISK($logfile_t)
End if

Here is a sample of the output:

Unique fields without index:
[Table_1]Field_1
[Table_2]Field_1
[Table_3]Field_1
[Table_4]Field_1