Calling all the SQL Guru's

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/