Tech Tip: Checking the size of all tables

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

Published On: May 14, 2018

The following method can be used to quickly obtain the total size (in megabytes) of all records in each table (ignoring Object fields).

C_LONGINT(tID;fID)
C_REAL($result_in_mb;$byte_per_bits;$megabyte_per_bytes;result_in_bits)

$byte_per_bits:=1/8
$megabyte_per_bytes:=1/1048576

// get table names
ARRAY LONGINT(al_tables;0)
Begin SQL
   select TABLE_ID from _USER_TABLES into :al_tables;
End SQL

// loop over tables
For ($a;1;Size of array(al_tables))
   C_TEXT(currentTable)
   tID:=al_tables{$a}
   currentTable:=Table name(tID)
   ARRAY LONGINT(al_fields;0)

    // get fields for current table
   Begin SQL
     select COLUMN_ID from _USER_COLUMNS where TABLE_ID = :tID into :al_fields;
   End SQL

    // build SQL statement
   $sql:="SELECT SUM ("
   For ($b;1;Size of array(al_fields))
     fID:=al_fields{$b}
     if(Type(field(tID;fID)->)#Is object)
       $sql:=$sql+"BIT_LENGTH (["+Field name(tID;fID)+"])"
        If ($b<Size of array(al_fields))
         $sql:=$sql+" + "
        End if
      end if
   End for
   $sql:=$sql+") FROM ["+currentTable+"] INTO :result_in_bits;"

    // execute built sql statement
   Begin SQL
     EXECUTE IMMEDIATE :$sql;
   end sql

    // calculate and alert the result
    $result_in_mb:=result_in_bits*$byte_per_bits*$megabyte_per_bytes
    $msg:=currentTable+" is "+String($result_in_mb)+" MB"
    alert($msg)
End for

When execute, the method above will present an ALERT for each table, notifying the user how much data is stored in that particular table (ignoring Object fields).