Re: Finding num_rows in user_tables

>In article <1995Oct13.154559.6996_at_venus.gov.bc.ca> gary.wong_at_cyberstore.ca
 writes:
>> alaw_at_us.oracle.com (Alvin W. Law) writes:
>> In article <45l661$iaq_at_pheidippides.axion.bt.co.uk> Darren Cook <dcook>
 writes:
>>
>> > >How can I find, using just one sql statement, the table_name and the
>> > >number of rows within each table ?

Was this not my question, originally ? :-) :-) :-)

I got a number of replies on email, and basically, all you need to do is an 'analyze table compute statistics' command. This would fill up all the NULLs in the table with the values. Then you can use a single SQL statement such as 'select table_name, num_rows from user_tables' or something like that. Thanks to all who replied...

>< snipped to save bandwidth>
>>>>>
>Darren,
>
>I usually spool (i.e. spool c:\temp\yutz.sql) the results of a query like:
>select 'select ''' || table_name ||
>''' as TblName, count(*) from ' || table_name ||';' from user_tables
>/
>
>This creates a sql script with commands like:
> select 'CATEGORY' as TblName, count(*) from CATEGORY;
                      

What would happen if the tables have null values ? The count(*) has to be done on a non-null column right ?
Thanks..
> select 'PARM' as TblName, count(*) from PARM;
                  

>....etc...
>
>Running this script will give you the results you want. I know that this isn't
 a 'one sql statement' solution,
>but it works for me... If there is an easier solution, pleeeeeze post it!
>
>Hope this helps,
>Gary Wong (gary.wong_at_cyberstore.ca)
>Information Technology Consultant
>(604) 360-1699 (Voice)
>(604) 384-1178 (Fax)

Nalini

Nalini Vallampati
UNIX Admin/Oracle DBA
AT&T Capital - GIS
Miamisburg, OH 45342 Received on Mon Oct 16 1995 - 00:00:00 CET