Re: Select Select?

Paul Theseira wrote:
>
> Stephen
>
> Try the following query...
>
> select table_name, count(*)
> from all_tables
> group by table_name
> having count(*) > 0
>
> Cheers!
>
> Paul.
> ________________
> Paul Theseira
> GID Australia
> paul_at_gid.com
> http://www.gid.com
> srea_at_uaex.edu wrote in article <870378731.3718_at_dejanews.com>...
> >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;
> >
> >However, the count part is not valid here, and, even if it was valid,
> >that second select would be looking for a table called "table_name"
> >instead of using the value of table_name as the table to count.
> >
> >How can I do this?
> >
> >Please reply to:
> >Stephen Rea <srea_at_uaex.edu>
> >
> >-------------------==== Posted via Deja News ====-----------------------
> > http://www.dejanews.com/ Search, Read, Post to Usenet
> >

Please, don't do this. This will give you a list of all tables with a count of 1. This statement simply lists all tables and the count will count the number of times that table appears in all_tables (which will be 1 - unless you have different accounts with the same tables in which case you will get the number of schema's the table exists in). You coul use dynamic PL/SQL, or if you're just running it as a report you could do the following:

set pagesize 0
set feedback off
spool temp.sql
select 'select ''' || table_name || ''', count(*) from ' ||

       table_name || ';'
  from user_tables
order by table_name;
spool off
start temp

I used user_tables which will work for all tables owned by the current user. If you look at the select statement it simply generates another select statement that looks like the following example:

select 'TABLENAME', count(*) from TABLENAME;

It generates a statement like that for every table which you spool to a file and then run. The setting of pagesize and feedback turn off the header information and number of rows retrieved confirmation so they do not appear in your spooled file that you are going to run.

Chris Halioris
Tactics, Inc. Received on Tue Aug 05 1997 - 00:00:00 CDT