SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK ON
SET PAGES 200
SET LINES 132

REM
REM || Title : show_rowcount.sql
REM ||
REM || Purpose : This script will perform a row count of all the tables in a schema.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : schema owner
REM ||
REM ||

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
t_table_name 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;

CURSOR user_tables_c1
IS
SELECT table_name
FROM user_tables
ORDER BY table_name;
BEGIN

t_limit := 0;

OPEN user_tables_c1;

LOOP
FETCH user_tables_c1 INTO t_table_name;
EXIT WHEN user_tables_c1%NOTFOUND;

t_command := 'SELECT COUNT(0) FROM ' || t_table_name;
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_table_name, 55, ' ') || TO_CHAR (t_total_records, '99999999') || ' record(s)');
END IF;

DBMS_SQL.close_cursor (t_cid);
END LOOP;

CLOSE user_tables_c1;
END;
/

SET SERVEROUTPUT ON
SET FEEDBACK ON