Live ORACLE

rem ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––-

rem Filename:   countall.SQL

rem Purpose:    Count the number of rows for ALL tables in current SCHEMA

rem             using PL/SQL

rem Date:       15-Apr-2000

rem Author:     Eberhardt, Roberto (Bolton) (reberhar@husky.ca)

rem ––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––––-

SET serveroutput on size 1000000


DECLARE

 t_c1_tname       user_tables.table_name%TYPE;

  t_command        VARCHAR2(200);

  t_cid            INTEGER;

  t_total_records  NUMBER(10);

  stat             INTEGER;

  row_count        INTEGER;

  t_limit          INTEGER := 0;    -- Only show tables with more rows

  CURSOR c1 IS

    SELECT   table_name

    FROM     user_tables

    ORDER BY table_name;

BEGIN

 t_limit := 0;



 OPEN c1;



 LOOP

   FETCH c1 INTO t_c1_tname;

 

   EXIT WHEN c1%NOTFOUND;

 

   t_command := 'SELECT COUNT(0) FROM '

                ||t_c1_tname;

 

   t_cid := dbms_sql.open_cursor;

 

   dbms_sql.Parse(t_cid,t_command,dbms_sql.native);

 

   dbms_sql.Define_column(t_cid,1,t_total_records);

 

   stat := dbms_sql.EXECUTE(t_cid);

 

   row_count := dbms_sql.Fetch_rows(t_cid);

 

   dbms_sql.Column_value(t_cid,1,t_total_records);

 

   IF t_total_records > t_limit THEN

     dbms_output.Put_line(Rpad(t_c1_tname,55,' ')

                          ||To_char(t_total_records,'99999999')

                          ||' record(s)');

   END IF;

 

   dbms_sql.Close_cursor(t_cid);

 END LOOP;



 CLOSE c1;

END;

/

Count the number of rows for ALL tables in current schema