SQL Script Count Table Rows

Updated : 1-June-1998
Version : 1.0

Description

This script lists the number of rows in all tables for a given schema. Can be useful of you've had a database import fail with constraint errors and some rows have not been imported. You can use this script to check against the log file of the database import.

Parameters

&1 - Table Owner

SQL Source

set serverout on size 1000000
set verify off
spool numrows_&&1..lst

declare
w_p_string varchar2(254);
w_cursor_id integer;
w_p_exec integer;
col1 number;
row_count number;
row1 number;
row2 number;
cursor get_tab is
select table_name
from dba_tables
where owner=upper('&&1');

begin

dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_&&1.lst ....');
dbms_output.put_line('....');

w_cursor_id:=dbms_sql.open_cursor;

for get_tab_rec in get_tab loop

BEGIN
w_p_string := 'select count(*) col1 from &&1..'||get_tab_rec.table_name;
dbms_sql.parse(w_cursor_id,w_p_string,1);

dbms_sql.define_column(w_cursor_id, 1, col1);
w_p_exec := dbms_sql.execute(w_cursor_id);
w_p_exec := dbms_sql.fetch_rows(w_cursor_id);
dbms_sql.column_value (w_cursor_id,1,row_count);

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,30)||' '||TO_CHAR(row_count)||' rows.');

exception when others then
dbms_output.put_line('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

dbms_sql.close_cursor(w_cursor_id);

end;
/
set verify on
spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal