Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Calling all the SQL Guru's

Re: Calling all the SQL Guru's

From: Simon Irvin <sirvin_at_synomics.com>
Date: Fri, 9 Mar 2001 09:43:51 -0000
Message-ID: <3aa8a87b_1@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 Fri Mar 09 2001 - 03:43:51 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US