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