RE: Count(*) each table from user

From: Ian Cary <Ian.Cary_at_ons.gsi.gov.uk>
Date: Fri, 2 Jul 2004 13:44:54 +0100
Message-ID: <OFC984F50A.6985F2B2-ON80256EC5.0045CAA7-80256EC5.00460406@ons.gov.uk>

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_at_freelists.org 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
-----------------------------------------------------------------

Received on Fri Jul 02 2004 - 07:41:33 CDT

RE: Count(*) each table from user_tables