Re: SQL to find out user table which count > 0!

From: bob mourning <bmourn#spamsucker_at_erols.com>
Date: Fri, 15 Jan 1999 02:22:30 -0500
Message-ID: <77mqad$rhp$1@winter.news.rcn.net>

You could write a PL/SQL program that uses the dbms_sql package to dynamically submit queries fo rowcount for each table.

In pseudo code...

declare cursor c1 is select * from user_tables; sSQL varchar2(255);
hCursor long; -- a cursor handle; see the package for exact req's

begin

    for each rec in c1 loop

        sSQL := 'select count(*) from ' || rec.table_name;
        --here you'd get a cursor handle, prepare, execute, fetch and close
within the lop
        -- then you could insert to a temp table or

dbms_output.put_line(rec.table_name || ' count:' || ....

    end loop;

alternatively, you could just use the dbms package that analyze's the schema to update the table stat's and then just select the count from the dba table.

Yeung Man wrote in message <77mois$ik1_at_news.hk.linkage.net>...
>Hi,
>
> I want to find out all user tables in our application system with
>rowcount > 0. I know I can do with the analysis and the query the dict.
But,
>can I just use SQL to find them out? TIA.
>
>Best Regards,
>Man
>
>yngm_at_chowsangsang.com
>
>
Received on Fri Jan 15 1999 - 01:22:30 CST