Counting rows for all tables in Firebird
Posted by jpluimers on 2019/08/28
Sometimes you want to count data in all tables on a database to get a feel for the orders of magnitude, but you cannot use the approximated example in How to speed up Count(*) in Interbase/Firebird – Stack Overflow as those require primary keys.
Then the below script can help: it generates the right queries as a union all ordering by the count in the tables.
Example generated code on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
SQL
--------------------------------------------------------------------------------------------------
select 128 as id, 'COUNTRY' as name, count(*) from "COUNTRY" union all
select 129 as id, 'JOB' as name, count(*) from "JOB" union all
select 130 as id, 'DEPARTMENT' as name, count(*) from "DEPARTMENT" union all
select 131 as id, 'EMPLOYEE' as name, count(*) from "EMPLOYEE" union all
select 133 as id, 'PROJECT' as name, count(*) from "PROJECT" union all
select 134 as id, 'EMPLOYEE_PROJECT' as name, count(*) from "EMPLOYEE_PROJECT" union all
select 135 as id, 'PROJ_DEPT_BUDGET' as name, count(*) from "PROJ_DEPT_BUDGET" union all
select 136 as id, 'SALARY_HISTORY' as name, count(*) from "SALARY_HISTORY" union all
select 137 as id, 'CUSTOMER' as name, count(*) from "CUSTOMER" union all
select 138 as id, 'SALES' as name, count(*) from "SALES" order by 3
Example output on C:\Program Files (x86)\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB:
ID NAME COUNT 133 PROJECT 6 128 COUNTRY 14 137 CUSTOMER 15 130 DEPARTMENT 21 135 PROJ_DEPT_BUDGET 24 134 EMPLOYEE_PROJECT 28 129 JOB 31 138 SALES 33 131 EMPLOYEE 42 136 SALARY_HISTORY 49
The generation code below uses a few tricks:
- [WayBack] How to get record count for all tables? and [WayBack] How to get a list of tables, views and columns in Firebird database?
Here’s query to list all user tables:
select rdb$relation_name
from rdb$relations
where rdb$view_blr is null
and (rdb$system_flag is null or rdb$system_flag = 0); - [WayBack] How to get record number or rank as part of dataset? (note that
IDthe negation ofSCORE)
The RANK of a row is simply the COUNT() + 1 of better-ranked rows. This can be computed with a
self-join:
SELECT p.name, p.score, COUNT(others.score) + 1 AS "rank"
FROM players p
LEFT JOIN players others ON others.score > p.score
GROUP BY 1, 2
ORDER BY "rank"
The rank helps me distinguish the last row (for the order by 3 clause) and other rows (for the union all clauses).
Generation code:
with tables(id, name) as (
-- http://www.firebirdfaq.org/faq376/
select r.RDB$RELATION_ID as id, trim(r.RDB$RELATION_NAME) as name
from RDB$RELATIONS r
where 1=1
and (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0)
and r.RDB$VIEW_BLR is null
order by 1
),
ranked_tables(id, rank, name) as (
-- http://www.firebirdfaq.org/faq343/
select tables.ID, count(others.id)+1 as "rank", tables.NAME
from tables
left join tables others on others.ID < tables.ID
group by "ID", "NAME"
order by "rank"
),
parts(id, rank, name, suffix) as (
select ranked_tables.id,
ranked_tables.rank,
ranked_tables.name,
case
when ranked_tables.rank = 1 then 'union all' -- first
when ranked_tables.rank = (select count(*) from tables) then 'order by 3' --last
else 'union all' -- middle
end as suffix
from ranked_tables
)
select -- parts.id, parts.rank, parts.name, parts.suffix,
'select '||parts.id||' as id, '''||parts.name||''' as name, count(*) from "'||parts.name||'" '||parts.suffix||'' as SQL
from parts
order by parts.id
–jeroen
Gist:
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| with tables(id, name) as ( | |
| — http://www.firebirdfaq.org/faq376/ | |
| select r.RDB$RELATION_ID as id, trim(r.RDB$RELATION_NAME) as name | |
| from RDB$RELATIONS r | |
| where 1=1 | |
| and (r.RDB$SYSTEM_FLAG is null or r.RDB$SYSTEM_FLAG = 0) | |
| and r.RDB$VIEW_BLR is null | |
| order by 1 | |
| ), | |
| ranked_tables(id, rank, name) as ( | |
| — http://www.firebirdfaq.org/faq343/ | |
| select tables.ID, count(others.id)+1 as "rank", tables.NAME | |
| from tables | |
| left join tables others on others.ID < tables.ID | |
| group by "ID", "NAME" | |
| order by "rank" | |
| ), | |
| parts(id, rank, name, suffix) as ( | |
| select ranked_tables.id, | |
| ranked_tables.rank, | |
| ranked_tables.name, | |
| case | |
| when ranked_tables.rank = 1 then 'union all' — first | |
| when ranked_tables.rank = (select count(*) from tables) then 'order by 3' –last | |
| else 'union all' — middle | |
| end as suffix | |
| from ranked_tables | |
| ) | |
| select — parts.id, parts.rank, parts.name, parts.suffix, | |
| 'select '||parts.id||' as id, '''||parts.name||''' as name, count(*) from "'||parts.name||'" '||parts.suffix||'' as SQL | |
| from parts | |
| order by parts.id |
–jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment