Check out the system tables fellas

guys....guys...

there is a system table called ALL_TABLES. in this table there is a column called NUM_ROWS in the same table there is also a column called OWNER...

catch my drift ??????

also a point referring to john doe's soln....never say count(*)....instead say count(1) or count('X')..it gives the same output but works much much faster...

ciao
venkat

"Simon Irvin" <sirvin_at_synomics.com> wrote in message news:3aa8a87b_1_at_nnrp1.news.uk.psi.net...
> How about using the row count generated generated by the analyze command?
>
> "John Doe" <jhon_at_doe.com> wrote in message
> news:98a5in$uke$1_at_news.btv.ibm.com...
> > 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 Sat Mar 10 2001 - 06:27:03 CST