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;

It would be helpful if you posted your errors instead of trying to make us guess. Fortunately you have made a couple of classic bloomers so it's an easy game this time. — APC, May 22 '12 at 15:27

score 75 · Answer 1 · answered May 22 '12 at 15:24

75

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'

answered May 22 '12 at 15:24

1

Perfect answer. Thank you :) – Anjana Silva Jun 03 '19 at 13:54
1

Take note that some table_name might be case-sensitive, so the inner-query might fail if you do not surround it in double-quotes, i.e. `'select count(1) c from '||owner||'."'||table_name ||'"'` – Aditya Santoso Jun 26 '20 at 07:05
Lovely stuff. That's the only solution that worked on Oracle 9. – neydroydrec Oct 06 '20 at 01:26

Tony Andrews · Accepted Answer · 2020-10-06T08:06:52.120

32

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.

edited Oct 06 '20 at 08:06

answered May 22 '12 at 15:22

Tony Andrews

129,880
21
220
259

1

Small typo: The l_count should in INSERT values should be v_count – ozzboy May 22 '12 at 15:50
3

A couple of variations to suggest ... I've recently grown fond of constructing the SQL to be executed in the implicit cursor's selet clause, as it allows you to run the select and see what statement is generated. One might also construct the insert statement as dynamic SQL and combine the insert and select into a single operation. Just a couple of options ... – David Aldridge May 22 '12 at 21:46
1

ORA-06550: line 10, column 21: PL/SQL: ORA-00942: table or view does not exist ORA-06550: line 10, column 9: PL/SQL: SQL Statement ignored – SherlockSpreadsheets Mar 22 '19 at 16:39
I got the same error as with @SherlockSpreadsheets here. – neydroydrec Oct 06 '20 at 01:25
@Benjamin Perhaps you don't have a table called `STATS_TABLE` like the OP does? This would be a table you would create yourself. – Tony Andrews Oct 06 '20 at 07:44
Okay I won't be able to do that in my case as I'm working on a read-only DB mode for that specific DB. Solution below worked out for me though. Thanks. – neydroydrec Oct 07 '20 at 05:55

score 11 · Answer 3 · answered May 12 '14 at 17:59

select owner, table_name, num_rows, sample_size, last_analyzed from all_tables;

This is the fastest way to retrieve the row counts but there are a few important caveats:

NUM_ROWS is only 100% accurate if statistics were gathered in 11g and above with ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE (the default), or in earlier versions with ESTIMATE_PERCENT => 100. See this post for an explanation of how the AUTO_SAMPLE_SIZE algorithm works in 11g.
Results were generated as of LAST_ANALYZED, the current results may be different.

score 6 · Answer 4 · answered Aug 30 '16 at 09:43

6

If you want simple SQL for Oracle (e.g. have XE with no XmlGen) go for a simple 2-step:

select ('(SELECT ''' || table_name || ''' as Tablename,COUNT(*) FROM "' || table_name || '") UNION') from USER_TABLES;

Copy the entire result and replace the last UNION with a semi-colon (';'). Then as the 2nd step execute the resulting SQL.

answered Aug 30 '16 at 09:43

Jörg

822
1
10
13

If there are many tables I think copying results is time-consuming. – GoingMyWay Oct 20 '17 at 03:15

score 2 · Answer 5 · answered Jul 18 '19 at 02:51

Get counts of all tables in a schema and order by desc

select 'with tmp(table_name, row_number) as (' from dual 
union all 
select 'select '''||table_name||''',count(*) from '||table_name||' union  ' from USER_TABLES 
union all
select 'select '''',0 from dual) select table_name,row_number from tmp order by row_number desc ;' from dual;

Copy the entire result and execute

score 1 · Answer 6 · answered May 22 '12 at 15:38

You have to use execute immediate (dynamic sql).

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; 
loop
    fetch get_tables into v_table_name,v_owner; 
    EXIT WHEN get_tables%NOTFOUND;
    execute immediate '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; 
end loop;
CLOSE get_tables; 
END;

Get counts of all tables in a schema

6 Answers6

Linked

Related