How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.
1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.
2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.
Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.
3) Update schema statistics and query user_tables view
SELECT table_name, num_rows FROM user_tables
This could be a time consuming process depending on the size of existing tables and number of indexes.
4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:
col "Records" for a10 SELECT table_name, dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records" FROM user_tables; TABLE_NAME Records ------------------------------ --------- DEPT 4 EMP 14 BONUS 0 SALGRADE 5
Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).
Suggested further reading:
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/