SQL Script Count Table Rows

Updated : 28-Mar-2002
Version : 2.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

sql_stmt varchar2(254);
row_count 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('....');

FOR get_tab_rec IN get_tab LOOP

BEGIN

  sql_stmt := 'select count(*) col1 from &&1..'||get_tab_rec.table_name;
  
  EXECUTE IMMEDIATE sql_stmt INTO 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;

END;
/
set verify on
spool off

Previous Oracle Version Links

Count Table Rows

Return to Index of SQL Scripts

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