Oracle Tip: Counting ROWS for all tables in a Schema

There are quiet a few methods to find out the count of records in the schema tables.

The most obvious one is to query USER_TABLES for a list of tables - and then append 'select count(*) from ' - save a an sql script and then run it -

Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name

Sorry, for those who don't like it.
Even I don't love it but it's OK as it kind of works.

The second option is using the package DBMS_STATS to gather statistics and then just use the table USER_TABLES and the value in the column NUM_ROWS is what will be the number of records. The same can also be used if ANALYZE TABLE is used.

The next one though is a beauty.
It's a wonderful way of generating the XML document then it parses the XML and gets the neat and clean list of the tables and the corresponding record list.

select table_name
,to_number(extractvalue(xmltype(
dbms_xmlgen.getxml(
'select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables;

Read this interesting article here about the query.


Personally the option to create a table and then store data into it by writing something like a PL/SQL or to save an output (as in option I) seem most boring and irritating to me.

So whats ur pick?
Any better ideas do let me know.