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).
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).
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).