Re: How to Find Total Rows by Owner

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Thu, 18 Mar 1999 16:12:40 +0800
Message-ID: <36F0B578.6D36@bhp.com.au>

Mike J McCall 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

Quick and dirty way:

select sum(num_rows)
from user_tables

but this is only as accurate as your last analyze...

You can have dynamic table names using DBMS_SQL.

Cheers

--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"Never wrestle a pig - you both get dirty and the pig likes it..." Received on Thu Mar 18 1999 - 02:12:40 CST