Re: Calling all the SQL Guru's

No,
this is the only way which will provide results which are *always* correct. The other option is analyze the tables and use the num_rows column from all_tables.
However, statistics easily get outdated.

Hth,

Sybrand Bakker, Oracle DBA

"Mick Rice" <mick_rice_at_my-deja.com> wrote in message news:9617ek$6p3$1_at_nnrp1.deja.com...
> I was trying to devise a single piece of sql to generate a list of all
> the tables in the database and a count of the number of rows on each
> table. I started out confident that I could code this easily using a
> subquery and 'group by' statement. However an hour later, and
> confidence diminished, I havn't been able to get the syntax right. The
> best I could come up with was the pretty lame looking query following
> which wasn't what I was aiming for at all.
>
> set pagesize 0;
> spool count.sql;
> select 'select count(*) from '||owner||'.'|| table_name||';' from
> all_tables;
> spool off;
>
> Can anyone come up with a single statement which does this ?
>
> It's put me out of my misery,
>
> Thanks,
>
> Mick.
>
> --
> Back it up !
>
>
> Sent via Deja.com
> http://www.deja.com/
Received on Fri Feb 09 2001 - 11:21:16 CST