Re: Select Select?

From: Mike Morgan <mmorga2_at_amfam.com>
Date: 1997/08/05
Message-ID: <01bca1a9$c8bdd400$7c38c8a5@mxm014pc>#1/1

Paul,

Wouldn't your solution tell you how many TABLES you have access to (ALL_TABLES) - along with the count of tables by name?

As an alternative how about:

set head off feed off pages 0
spool count_tables.sql
select 'select count(*) '||table_name||' from '||table_name||';' from user_tables;

spool off
set head on feed on pages 9999
@count_tables

You could also use the all_tables/dba_tables views.- If you do, you should also include owner -eg:
  select 'select count(*) '||owner||'.'||table_name||' from '||owner||'.'||table_name||';'
  from all_tables;

Note that the first time "table_name" is referenced, it is used as a column alias. This is how you can tell which table the count(*) refers to. Alternatively, you could just set echo on and the SQL (and thus the table_name) to determine which table's rows are being counted at a given time.

HTH,
Mike Morgan
Compuware Corp.

---

Paul Theseira <paul_at_gid.com> wrote in response to Stephen ....
> Try the following query...
>
> select table_name, count(*)
> from all_tables
> group by table_name
> having count(*) > 0

Stephen originally wrote ....
> >I want to get a list of all tables and the number of records in those
> >tables for just the tables that have records in them. Something
> >like:
> >
> >select table_name,(select count(*) from table_name) nrecs
> >from all_tab_comments having nrecs > 0 order by table_name;
> >
Received on Tue Aug 05 1997 - 00:00:00 CDT