Query to get record count of all tables in a schema

Use this query to get the record count of all tables in a schema.

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

The output is like

table_name count
----------- --------
DEPT 4
EMP 14


Courtesy: http://laurentschneider.com/wordpress/2007/04/
how-do-i-store-the-counts-of-all-tables.html
Posted by Labels: , , ,

14 comments :

  1. AnonymousApril 19, 2007 at 5:17 PM

    really brillient

    ReplyDelete
  2. AnonymousApril 19, 2007 at 5:17 PM

    really brillient

    ReplyDelete
  3. ddfApril 24, 2007 at 7:01 PM

    1 select
    2 table_name,
    3 to_number(
    4 extractvalue(
    5 xmltype(
    6 dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')
    ) count
    7* from user_tables order by 1
    SQL> /
    dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cou
    nt
    *
    ERROR at line 6:
    ORA-19202: Error occurred in XML processing
    ORA-00911: invalid character
    ORA-06512: at "SYS.DBMS_XMLGEN", line 176
    ORA-06512: at line 1

    Not so brilliant. Using Oracle 10.2.0.2.

    ReplyDelete
  4. ddfApril 24, 2007 at 7:02 PM

    1 select
    2 table_name,
    3 to_number(
    4 extractvalue(
    5 xmltype(
    6 dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')
    ) count
    7* from user_tables order by 1
    SQL> /
    dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cou
    nt
    *
    ERROR at line 6:
    ORA-19202: Error occurred in XML processing
    ORA-00911: invalid character
    ORA-06512: at "SYS.DBMS_XMLGEN", line 176
    ORA-06512: at line 1

    Not so brilliant. Using Oracle 10.2.0.2.

    ReplyDelete
  5. Anantha NarayananApril 25, 2007 at 11:06 AM

    Have you given execute permission to dbms_xmlgen to your user. By default it must be there. But check any way.

    ReplyDelete
  6. Satyaki DeAugust 28, 2008 at 9:50 PM

    I've to say regarding this -

    Query to get record count of all tables in a schema

    Perhaps this has been shown first in otn forum by michael (Now his id is shown as michael2 in the new updated forum).

    ReplyDelete
  7. AnonymousDecember 3, 2008 at 11:46 PM

    Anantha:
    This works great in Oracle9, although does not work in Oracle8.
    Returns Error saying extractvalue is invalid. Any ideas???

    ReplyDelete
  8. AnonymousDecember 12, 2008 at 7:17 AM

    Thank you - worked for me w Oracle 11g

    ReplyDelete
  9. AnonymousSeptember 26, 2009 at 1:50 AM

    Thanks Anantha. It's Cool

    ReplyDelete
  10. AnonymousJanuary 14, 2010 at 1:01 AM

    select table_name, num_rows from user_tables

    ReplyDelete
  11. AnonymousApril 9, 2010 at 12:02 AM

    Excellent thought!!! I used this logic to get record count for all the tableNames from a config table.

    ReplyDelete
  12. AnonymousApril 13, 2010 at 9:28 AM

    SELECT owner,
    table_name,
    TO_NUMBER (
    EXTRACTVALUE (
    xmltype (
    DBMS_XMLGEN.
    getxml (
    'select nvl(count(*),0) c from ' || owner || '.' || table_name)),
    '/ROWSET/ROW/C'))
    COUNT
    FROM dba_tables
    where owner='FNSONLD'
    ORDER BY 1

    ReplyDelete
  13. AnonymousSeptember 4, 2010 at 5:58 PM

    You could have done it in one line.

    select sum(num_rows) from user_tables;

    ReplyDelete
  14. priyaJune 22, 2011 at 5:12 PM

    Very very brilliant! Thanks a lot!

    ReplyDelete

Subscribe to: Post Comments ( Atom )