How can I measure the amount of space occupied by blobs in the Firebird 2.1 database?

Question

I have a production database using Firebird 2.1 where I need to find out how much space is used for each table, including drops. The blob component is complex because it does not extend to a standard statistical report.

I do not have easy access to the server desktop, so installing UDF, etc. not a good solution.

How can i make it easy?

Answer 1

You can calculate the total size of all BLOB fields in the database with the following expression:

EXECUTE BLOCK RETURNS (BLOB_SIZE BIGINT) AS DECLARE VARIABLE RN CHAR(31) CHARACTER SET UNICODE_FSS; DECLARE VARIABLE FN CHAR(31) CHARACTER SET UNICODE_FSS; DECLARE VARIABLE S BIGINT; BEGIN BLOB_SIZE = 0; FOR SELECT r.rdb$relation_name, r.rdb$field_name FROM rdb$relation_fields r JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name WHERE f.rdb$field_type = 261 INTO :RN, :FN DO BEGIN EXECUTE STATEMENT 'SELECT SUM(OCTET_LENGTH(' || :FN || ')) FROM ' || :RN || ' WHERE NOT ' || :FN || ' IS NULL' INTO :S; BLOB_SIZE = :BLOB_SIZE + COALESCE(:S, 0); END SUSPEND; END 

Answer 2

I modified the Andrej code example to show the size of each blob field, not just the sum of all the blocks.

And used SET TERM so you can copy and paste this snippet directly into tools like FlameRobin.

 SET TERM #; EXECUTE BLOCK RETURNS (BLOB_SIZE BIGINT, TABLENAME CHAR(31), FIELDNAME CHAR(31) ) AS DECLARE VARIABLE RN CHAR(31) CHARACTER SET UNICODE_FSS; DECLARE VARIABLE FN CHAR(31) CHARACTER SET UNICODE_FSS; DECLARE VARIABLE S BIGINT; BEGIN BLOB_SIZE = 0; FOR SELECT r.rdb$relation_name, r.rdb$field_name FROM rdb$relation_fields r JOIN rdb$fields f ON r.rdb$field_source = f.rdb$field_name WHERE f.rdb$field_type = 261 INTO :RN, :FN DO BEGIN EXECUTE STATEMENT 'SELECT SUM(OCTET_LENGTH(' || :FN || ')) AS BLOB_SIZE, ''' || :RN || ''', ''' || :FN || ''' FROM ' || :RN || ' WHERE NOT ' || :FN || ' IS NULL' INTO :BLOB_SIZE, :TABLENAME, :FIELDNAME; SUSPEND; END END # SET TERM ;# 

This example does not work with ORDER BY, perhaps a more elegant solution without EXECUTE BLOCK exists.