Count number of rows for multiple tables in one query

Question

so I have a large number of tables which fall under a certain category. These tables all start with B1T(so B1T00001, B1T0002 etc). I've been tasked with providing some information on this set of tables.

Here is my initial SQL to get a count of how many B1T tables there are.

select obj_nm from od_md_obj
where proj_id = '6' and obj_nm like ('%B1T%')and obj_typ_id = '9';

This returns about 260 tables. I am attempting to get a count of the number of rows for all the tables combined. Either the query can break it the count by table, possibly use a rollup for the total count; or by just doing a single count for everything.

I tried looking at some of the solutions in my search but it requires a bit of tedious code considering the number of tables I am querying.

Any help would be greatly appreciated!!

I am using SQL developer currently.

Looks like you are struggling with a horrible database design. A table should represent an entity, like a user, an order, a supplier, a membership, whatever. There would be no two user tables or two order tables. If there is more data, you would add data to the one table rather than adding a new table. But this seems to be the case in your database. Why is there not only one B1T table? Then counting records would be super-simple. — Thorsten Kettner
– Thorsten Kettner, Commented Mar 31, 2016 at 20:02

krokodilko · Accepted Answer · 2016-03-31 20:34:26Z

Create a query that will generate a query for you.
For example this query:

select 'SELECT ''' || table_name || ''' as table_name, count(*) As con ' ||
       'FROM ' || table_name || 
        CASE WHEN lead(table_name) OVER (order by table_name ) IS NOT NULL 
        THEN ' UNION ALL ' END
from user_tables 
where table_name like 'B1T%'
;

will generate a result like this:

SELECT 'B1T00000' as table_name, count(*) As con FROM B1T00000 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00001' as table_name, count(*) As con FROM B1T00001 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00002' as table_name, count(*) As con FROM B1T00002 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00003' as table_name, count(*) As con FROM B1T00003 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00004' as table_name, count(*) As con FROM B1T00004 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00005' as table_name, count(*) As con FROM B1T00005 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00006' as table_name, count(*) As con FROM B1T00006 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00007' as table_name, count(*) As con FROM B1T00007 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00008' as table_name, count(*) As con FROM B1T00008 UNION ALL  
....
....
....
SELECT 'B1T00257' as table_name, count(*) As con FROM B1T00257 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00258' as table_name, count(*) As con FROM B1T00258 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00259' as table_name, count(*) As con FROM B1T00259 UNION ALL                                                                                                                                                                                                                                                  
SELECT 'B1T00260' as table_name, count(*) As con FROM B1T00260

And now just copy this result, paste it into you client (SQL-Developer etc.) and run it.
You will get desired results:

TABLE_NAME        CON
---------- ----------
B1T00000           65 
B1T00001           66 
B1T00002           67 
B1T00003           68 
B1T00004           69
...
...
...
B1T00256          321 
B1T00257          322 
B1T00258          323 
B1T00259          324 
B1T00260          325 

 261 rows selected

This is very similar to my SQL Server solution. I've forgotten all my Oracle syntax though! Nice work!

user5683823 · Accepted Answer · 2016-04-01 00:46:22Z

Which version of Oracle? Oracle 12 allows functions in a with clause; so a possible solution is

WITH FUNCTION count_rows(t_name IN VARCHAR2) RETURN NUMBER  
AS  
   l_rows NUMBER;  
BEGIN  
   EXECUTE IMMEDIATE 'SELECT COUNT(*)  FROM ' || t_name INTO l_rows;  
   RETURN l_rows;  
END;  
SELECT table_name, count_rows(table_name) as row_count
   FROM user_tables 
   WHERE table_name LIKE 'B1T%'

In earlier versions, you can do the same with a PL/SQL function that you must compile first, like this:

CREATE OR REPLACE FUNCTION count_rows(t_name IN VARCHAR2) RETURN NUMBER  
AS  
  l_rows NUMBER;  
BEGIN  
  EXECUTE IMMEDIATE 'SELECT COUNT(*)  FROM ' || t_name INTO l_rows;  
  RETURN l_rows;  
END;  
/

Compile it, then use it in a SELECT statement identical to the one above.

If you only need the grand total, you can use

SELECT SUM(row_count) FROM (SELECT .....)

where the subquery is the query shown in the first code snippet.

Collectives™ on Stack Overflow

Count number of rows for multiple tables in one query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related