Help with counting records in atable

Hi,

I have created around 40 tables in a database. Is there a way I can count total records in all the tables at a time instead of doing table by table?

Your help is appreciated.

Thanks,

Hi,
You can analyze those tables and select num_rows from dba_tables.
:)

That however will return the number of rows at the moment when the tables are analyzed, not the actual number of rows.
Not to mention that the number might be inconsistent, since the number of rows of table 1 will be counted at one moment and in table 2 in another
What about

select (select count(*) from employees)+(select count(*) from departments
from dual

That is doing again counting table by table, but the result is aggregated by the outer select

Quote:

---

Originally Posted by Bore

select (select count(*) from employees)+(select count(*) from departments
from dual

---

One empty table will spoil that for you -- use NVL(count(*),0).

you could also ...

Quote:

---

select count(*) from
(
select null from employees
union all
select null from departments
union all
...
)

---

Quote:

---

Originally Posted by ujjwala

Hi,

I have created around 40 tables in a database. Is there a way I can count total records in all the tables at a time instead of doing table by table?

Your help is appreciated.

Thanks,

---

Only 40 tables? If there's not too many rows (ie) not 100,000,000 then do a quick Export and the logfile will show how many rows in each table.

Dave,
count(*) from empty table is still 0 not null. It is good to think about nulls in arithmetic expressions but no need of nvl here