HOWTO: Count all Rows in all Tables – The XMLDB Way

Someone beat me to it in this good post: Oracle Tip: Counting ROWS for all tables in a Schema . So here a reminder for me where to find it.

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

That said, I don’t like the use of DBMS_XMLGEN (performance reasons/compatibility), so also for future reference, I also still have to come up with a better one. The use of extractvalue has at least the advantage that this one should be backwards compatible to 9.2.0.3.

Written by: