Get counts of all tables in a schema

Question

I am trying to get the record counts of all tables in a schema. I am having trouble writing the PL/SQL. Here is what I have done so far, but I am getting errors. Please suggest any changes:

DECLARE v_owner varchar2(40); v_table_name varchar2(40);  cursor get_tables is select distinct table_name,user from user_tables where lower(user) = 'SCHEMA_NAME';   begin  open get_tables; fetch get_tables into v_table_name,v_owner;      INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)     SELECT v_table_name,v_owner,COUNT(*),TO_DATE(SYSDATE,'DD-MON-YY') FROM         v_table_name;  CLOSE get_tables;  END; 

Answer 1

This can be done with a single statement and some XML magic:

select table_name,         to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as count from all_tables where owner = 'FOOBAR' 

Answer 2

This should do it:

declare     v_count integer; begin      for r in (select table_name, owner from all_tables               where owner = 'SCHEMA_NAME')      loop         execute immediate 'select count(*) from ' || r.table_name              into v_count;         INSERT INTO STATS_TABLE(TABLE_NAME,SCHEMA_NAME,RECORD_COUNT,CREATED)         VALUES (r.table_name,r.owner,v_count,SYSDATE);     end loop;  end; 

I removed various bugs from your code.

Note: For the benefit of other readers, Oracle does not provide a table called STATS_TABLE, you would need to create it.