Re: A query to return row counts of all tables in cat

Mladen Gogala wrote:
> On Thu, 30 Nov 2006 12:48:01 -0800, erich.pearson wrote:
>
> > I'm trying to craft a query that will return all the tables listed in
> > cat, along with the row count of each. Something like this:
> >
> > TABLE_NAME COUNT(*)
> > --------------------- -------------
> > TEST 2257
> > CYCLE 308
> > ACTIONS 6593
> > . . .
> >
> > I tried:
> >
> > SELECT TABLE_NAME, COUNT(*) FROM(SELECT TABLE_NAME FROM CAT)
> > GROUP BY TABLE_NAME;
> >
> > but it just returns 1 for the row count.
> >
> > Is this even possible to do without programming? Can someone please
> > help me out?
> >
> > Thanks!
> > Erich Pearson
>
> begin
> DBMS_STATS.GATHER_SCHEMA_STATS ( ownname=> 'SCOTT',
> estimate_percent => NULL);
> end;
> /
> select table_name,num_rows from user_tables;
>
>
> If you have very large tables, this will take forever or even longer than
> that.
>
> --
> http://www.mladen-gogala.com

Here is a link to an article with pl/sql code to count every table in the db or owned by a specific user. Just modify the driving query.

Is there a simple way to produce a report of all tables in the database with current number of rows ?
http://www.jlcomp.demon.co.uk/faq/count_all_rows.html

HTH -- Mark D Powell -- Received on Thu Nov 30 2006 - 18:55:45 CST