Aproximated record count

Ivan Prenosil posted in Firebird support list an interesting way to retrieve the aproximated record count in a table, without using the “slow” select count(*). See the code:

Is there a fast way so that i could see a list of all my tables and it’s number of records.

There are many ways.
E.g. if you do not need absolutely exact counts, and all your tables
have primary keys, you can count number of entries in primary keys
(the method will count even not yet commited data, or deleted
but not garbage collected).

First, refresh statistics for all your indexes:

UPDATE RDB$INDICES SET RDB$STATISTICS = -1;
COMMIT;

Then display table names and record counts:

SELECT RDB$RELATIONS.RDB$RELATION_NAME,
CASE WHEN RDB$INDICES.RDB$STATISTICS=0 THEN 0
ELSE CAST(1 / RDB$INDICES.RDB$STATISTICS AS INTEGER) END
FROM RDB$RELATIONS
LEFT JOIN RDB$RELATION_CONSTRAINTS
ON RDB$RELATIONS.RDB$RELATION_NAME=RDB$RELATION_CONSTRAINTS.RDB$RELATION_NAME
AND RDB$CONSTRAINT_TYPE='PRIMARY KEY'
LEFT JOIN RDB$INDICES ON
RDB$RELATION_CONSTRAINTS.RDB$INDEX_NAME=RDB$INDICES.RDB$INDEX_NAME
WHERE RDB$VIEW_BLR IS NULL
AND RDB$RELATION_ID >= 128
ORDER BY 1

Ivan
http://www.volny.cz/iprenosil/interbase/