PageRenderTime 23ms CodeModel.GetById 18ms app.highlight 4ms RepoModel.GetById 0ms app.codeStats 0ms

/Test/countall.sql

http://github.com/fishman/ctags
SQL | 43 lines | 40 code | 3 blank | 0 comment | 2 complexity | baa309dc30f12ac669a981250f037859 MD5 | raw file
Possible License(s): GPL-2.0
 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/