Row count of all the table under any schema

Some times we may get requirement to gather the row count of all the tables under any specific schema.

So there is a very easy way to get the same detail as below.

Query :

SELECT 

table_name, 

TO_NUMBER( 

EXTRACTVALUE( 

XMLTYPE( 

DBMS_XMLGEN.getxml('SELECT COUNT(*) c FROM '||table_name)) 

,'/ROWSET/ROW/C')) cnt 

FROM user_tables order by 

table_name; 

Demonstration :

SQL> show user

USER is "BRIJ"

SQL> set lines 300 pages 1000

SQL> col TABLE_NAME for a22

SQL> SELECT

table_name,

TO_NUMBER(

EXTRACTVALUE(

XMLTYPE(

DBMS_XMLGEN.getxml('SELECT COUNT(*) c FROM '||table_name))

,'/ROWSET/ROW/C')) cnt

FROM user_tables order by

table_name;

TABLE_NAME                    CNT

---------------------- ----------

COUNTRY                         4

DPT                             8

EMP                            20

JOBS                           12

JOB_GRADE                       6

JOB_HIST                       10

LOC                             5

REGION                          4

 

8 rows selected.