I wrote this a few years back for 7.* and it still works OK. I think I'd probably be using a bulk collect and execute immediate if I was doing it today but "If it ain't broke....." Cheers, Ian create or replace procedure cnt_rows(tabowner varchar2 default user, tabname varchar2 default '%') is cnt_tabs integer; tab_cnt integer; num_rows integer; num_tabs integer; cursor get_tabs is select owner, table_name from dba_tables where owner like upper(tabowner) and table_name like upper(tabname) order by owner,table_name; begin dbms_output.enable(1000000); dbms_output.put_line(' '); dbms_output.put_line(' '); dbms_output.put_line('Count of rows in tables owned by '||tabowner); dbms_output.put_line(' '); if instr(tabowner,'%') > 0 then dbms_output.put(rpad('Owner',20)||' '); end if; dbms_output.put_line(rpad('Table Name',30)||' '||'Number of Rows'); if instr(tabowner,'%') > 0 then dbms_output.put(rpad('-',20,'-')||' '); end if; dbms_output.put_line(rpad('-',30,'-')||' '||'--------------'); num_tabs := 0; cnt_tabs := dbms_sql.open_cursor; for tabrec in get_tabs loop dbms_sql.parse(cnt_tabs,'select count(*) from '||tabrec.owner ||'."'||tabrec.table_name||'"',dbms_sql.native); dbms_sql.define_column(cnt_tabs,1,num_rows); tab_cnt := dbms_sql.execute_and_fetch(cnt_tabs); num_tabs := num_tabs + tab_cnt ; dbms_sql.column_value(cnt_tabs,1,num_rows); if instr(tabowner,'%') > 0 then dbms_output.put(rpad(tabrec.owner,20)||' '); end if; dbms_output.put_line(rpad(tabrec.table_name,30)||' '||lpad(num_rows,14)); end loop; dbms_sql.close_cursor(cnt_tabs); dbms_output.put_line(' '); dbms_output.put_line('Number of tables selected: '||num_tabs); end; For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ********************************************************************** Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ********************************************************************** Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ********************************************************************** ______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email ______________________________________________________________________ ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------