RE: Count(*) each table from user_tables

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: