The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,119 other followers

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:

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:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: