Re: How to Find Total Rows by Owner

In article <7cpgfs$dan$1_at_nnrp1.dejanews.com>,   Mike J McCall <mjmccall_at_my-dejanews.com> wrote:
> Is there any way to find the total number of rows in all the tables for one
> owner ? In other words, how can I do an SQL "select count(*) from
> table1,table2,table3,...' that prints one number representing the combined
> number of rows in all tables?
>
> I don't think a join or union is the answer. I also tried a cursor in PL/SQL
> that dynamically selects individual counts from each table, but this won't
> compile because the tablename cannot be a variable. I am looking to use the
> total number of rows as a simple aggregate metric for all owned tables.
>
> Any ideas thanks in advance.
>
> Software Developer from the Past
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>

You can generate static sql dynamically and run the sql file like this:

set pagesize 0
set echo off
set verify off
set feedback off
set linesize 512
set trimspool on
set termout off

spool static.sql

prompt 'select sum(cnt) from ('
select 'select count(*) cnt from ' || table_name || ' union all ' from dba_tables where owner = upper('&&1'); prompt 'select 0 cnt from dual);'

spool off

!grep 'select' static.sql >static1.sql
@static1

exit

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Mar 19 1999 - 01:22:10 CST