RE: Quick select question

From: Daemen, Remco <R.Daemen_at_facent.nl>
Date: Wed, 20 Jun 2001 09:35:57 -0700
Message-ID: <F001.0033080A.20010620094941@fatcity.com>

Hi
Lee,
<SPAN

class=624014616-20062001> 
Here's
how you can do it in PL/SQL. It can easily be converted to SQL if realy realy want it :).
<SPAN

class=624014616-20062001> 
set
serveroutput on size 1000000declare cursor c is select table_name from user_tables; rec       
c%rowtype;begin  open c;  fetch c into
rec;
 
 
dbms_output.put_line('select ');  dbms_output.put_line( 'select '||''''||rec.table_name||''''||'

table_name'                      

||',count(*) from

'||rec.table_name                      

);  fetch c into rec;
 
 
while c%found   loop    dbms_output.put_line( 'union all

'||chr(10)                        
||'select 
'||''''||rec.table_name||''''                        

||',count(*) from

'||rec.table_name                        

);    fetch c into rec;  end
loop;
 
 
dbms_output.put_line(';');end;/
<SPAN

class=624014616-20062001> 
<SPAN

class=624014616-20062001>HTH,  Remco

  <FONT face=Tahoma
  size=2>-----Oorspronkelijk bericht-----Van: Robertson Lee - lerobe   [mailto:lerobe_at_acxiom.co.uk]Verzonden: woensdag 20 juni 2001   19:17Aan: Multiple recipients of list ORACLE-LOnderwerp:   Quick select question
  <SPAN
  class=625121716-20062001>All,
  <SPAN
  class=625121716-20062001> 
  Anyone know how to
  get a list of tablenames and the count of rows in them   <SPAN
  class=625121716-20062001> 
  <SPAN
  class=625121716-20062001>TABLE_NAME    COUNT
  <SPAN
  class=625121716-20062001>===========   ======   <SPAN

  class=625121716-20062001>LEE                        

  10
  <SPAN

  class=625121716-20062001>LEE1                       

  25
  <SPAN

  class=625121716-20062001>LEE2                      
  17................etc etc

  <SPAN
  class=625121716-20062001> 
  I know it can be
  done if the tables are analyzed and from user_tables but was wanting to know   how to do it from sqlplus.
  <SPAN
  class=625121716-20062001> 
  <SPAN
  class=625121716-20062001> 
  Tru64   

  <SPAN
  class=625121716-20062001>8.0.5.0.0
  <SPAN
  class=625121716-20062001> 
  <SPAN
  class=625121716-20062001> 
  <SPAN
  class=625121716-20062001>TIA
  <SPAN
  class=625121716-20062001> 
  <SPAN
  class=625121716-20062001>Lee   

   The information contained in this
  communication isconfidential, is intended only for the use of the   recipientnamed above, and may be legally privileged. If the reader of   this message is not the intended recipient, you arehereby notified that   any dissemination, distribution orcopying of this communication is   strictly prohibited. If you have received this communication in error,   please re-send this communication to the sender and delete the   original message or any copy of it from your computersystem. Received on Wed Jun 20 2001 - 11:35:57 CDT