tests/ctags/countall.sql

   1 rem -----------------------------------------------------------------------
   2 rem URL:        http://www.orafaq.com/scripts/plsql/countall.txt
   3 rem Filename:   countall.sql
   4 rem Purpose:    Count the number of rows for ALL tables in current schema
   5 rem             using PL/SQL
   6 rem Date:       15-Apr-2000
   7 rem Author:     Eberhardt, Roberto (Bolton) (reberhar@husky.ca)
   8 rem -----------------------------------------------------------------------
   9
  10 set serveroutput on size 1000000
  11
  12 DECLARE
  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;
  21 BEGIN
  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;
  42 END;
  43 /