Count Rows In All Tables

Description: Count the number of rows for ALL tables in current schema.

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;
/

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.