/*Script to count the number of rows in tables*/
set serveroutput on
declare
numrows integer;
cursor c1 is select table_name from user_tables order by table_name;
function rowcount(tablename in user_tables.table_name%type)
return integer is
cursornum integer;
numrows integer;
ignore integer;
begin
cursornum := dbms_sql.open_cursor;
dbms_sql.parse(cursornum,
'select count(*) from ' || tablename,
dbms_sql.v7);
dbms_sql.define_column(cursornum, 1, numrows);
ignore := dbms_sql.execute(cursornum);
ignore := dbms_sql.fetch_rows(cursornum);
dbms_sql.column_value(cursornum, 1, numrows);
dbms_sql.close_cursor(cursornum);
return numrows;
end;
begin
dbms_output.enable(100000);
dbms_output.put_line('Table Rows ');
dbms_output.put_line('—————————— ———-');
for c1rec in c1 loop
numrows := rowcount(c1rec.table_name);
dbms_output.put_line(rpad(c1rec.table_name, 32) || numrows);
end loop;
end;
/
