Re: Select count(*)

Hi

I'm not quite sure what you guys think
you're doing, but I do know it's not going to work!

You can select count(*) from user_tables until you're blue in the face, but it's not going to tell you how many rows are in the individual tables - that information is just not there. (Incidentally, count(1) is more efficient than count(*)).

To do this in a SQL query you would need a dynamic SQL function to check each table rerieved from user_tables. But as you can't embed dynamic SQL in SQL that's out.

What you could do is write a PL/SQL
procedure that reads user_tables, passes the table names to a dynamic SQL function and then uses DBMS_OUPUT to display
the tables with no rows.

John

>Johnny Taxén wrote:
>>
>> Hello
>>
>> I´m trying to do, probably a very simple SELECT command. But i just
>> can´t
>> get it to work. I want to do a SELECT COUNT(*) that displays only the
>> tables that have any rows.
>>
>> Regards/Johnny T
>
>Hi,
>
>
>ever tried
>
>select
> a.table_name, a.number_rows
>from
> (select
> count(*) number_rows, table_name
> from
> user_tables
> group by
> table_name
> ) a
>where
> a.number_rows > 0
>; ?
>
>--
>Regards
>
>Matthias Gresz :-)
>
>
>
>
Received on Fri Nov 28 1997 - 00:00:00 CST