1rem -----------------------------------------------------------------------
2rem URL:        http://www.orafaq.com/scripts/plsql/countall.txt
3rem Filename:   countall.sql
4rem Purpose:    Count the number of rows for ALL tables in current schema
5rem             using PL/SQL
6rem Date:       15-Apr-2000
7rem Author:     Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
8rem -----------------------------------------------------------------------
9
10set serveroutput on size 1000000
11
12DECLARE
13  t_c1_tname      user_tables.table_name%TYPE;
14  t_command       varchar2(200);
15  t_cid           integer;
16  t_total_records number(10);
17  stat            integer;
18  row_count       integer;
19  t_limit         integer := 0;    -- Only show tables with more rows
20  cursor c1 is select table_name from user_tables order by table_name;
21BEGIN
22  t_limit := 0;
23  open c1;
24  loop
25        fetch c1 into t_c1_tname;
26        exit when c1%NOTFOUND;
27        t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
28        t_cid := DBMS_SQL.OPEN_CURSOR;
29        DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
30        DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
31        stat := DBMS_SQL.EXECUTE(t_cid);
32        row_count := DBMS_SQL.FETCH_ROWS(t_cid);
33        DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
34        if t_total_records > t_limit then
35                DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
36                        to_char(t_total_records,'99999999')||' record(s)');
37
38        end if;
39        DBMS_SQL.CLOSE_CURSOR(t_cid);
40  end loop;
41  close c1;
42END;
43/
44