How to get the row count for all tables in a schema?
How to get the row count in each table of a schema?
Query
SQL> select
table_name,
to_number(
extractvalue(
xmltype(
dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
,’/ROWSET/ROW/C’)) count
from all_tables where owner=’SYSTEM’;
SQL>
Output
TABLE_NAME COUNT
——————————— ———-
ACCESS_APPL_DATA 21
ACCESS_APPL_PGM 2
AQ$_INTERNET_AGENTS 4
AQ$_INTERNET_AGENT_PRIVS 3
AQ$_QUEUES 22
AQ$_QUEUE_TABLES 12
AQ$_QUEUE_UPGRADE_TMP 4
AQ$_SCHEDULES 0
See also