Re: Calling all the SQL Guru's

Try this it's a bit more simple and it does the same

select 'select count(*) from '||owner||'.'|| table_name||';' from dba_all_tables;

Regards
John D

"Martin Haltmayer" <Martin_Haltmayer_at_d2mail.de> wrote in message news:3AA7C4A5.A3D4614E_at_d2mail.de...
> Try this:
>
> create or replace
> function my_count (i_tabname in user_tables.table_name%type) return number
 is
> type t is ref cursor;
> r t;
> l_retval number;
> begin
> open r for 'select count (*) from "' || i_tabname || '"';
> fetch r into l_retval;
> close r;
> return l_retval;
> exception
> when others then
> if r%isopen then
> close r;
> end if; -- r%isopen then
> raise;
> end my_count;
> /
> show errors
>
> select table_name, my_count (table_name)
> from user_tables
> order by 1;
>
> I tested it on NT 4.0, Oracle 8.1.6.3.1
>
> Martin
>
>
>
> Mick Rice wrote:
> >
> > 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 Mar 09 2001 - 02:48:45 CST