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

From: Mladen Gogala <mgogala.spam-me-not_at_verizon.net>
Date: Thu, 30 Nov 2006 22:14:11 GMT
Message-Id: <pan.2006.11.30.22.14.10.535731@verizon.net>

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

Received on Thu Nov 30 2006 - 16:14:11 CST