Count(*) from all tables
Angus McTavish aged 75, September 03, 2003 - 7:03 am UTC
Run this in sqlplus to get a count of all tables:
store set store replace
set lines 100
set head off
set feed off
set term off
set wrap off
COLUMN DUMCOL FORMAT A30 TRUNC
spool cnt.sql
select 'select '''||table_name||''' DUMCOL, count(*) from '||table_name||';' from user_tables
/
spool off
spool counts.lst
@cnt
spool off
CLEAR COLUMNS
@store
ed counts.lst
IMHO -- Function is better
Marcio, September 03, 2003 - 9:58 am UTC
I think more easer function -- to get all of count(*)
Consider:
Tables rows
-----------------------------------------------------
LOCALIDADE 24275
LOCAL_ANATEL 37000
LOCAL_DETRAF 24275
*-------------------
* script method
*-------------------
store set store replace
set lines 100
set head off
set feed off
set term off
set wrap off
COLUMN DUMCOL FORMAT A30 TRUNC
spool cnt.sql
select 'select '''||table_name||''' DUMCOL, count(*) from '||table_name||';'
from user_tables
where table_name like 'LOCA%'
/
spool off
spool counts.lst
@cnt
spool off
CLEAR COLUMNS
@store
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 3
Fetch 9 0.62 0.61 0 821 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 0.62 0.62 0 821 0 10
*----------------------
* Function Method:
*----------------------
select table_name, get_rows(table_name)
from user_tables
where table_name like 'LOCA%'
/
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.01 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.65 0.63 0 771 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.66 0.64 0 771 0 4
--
Anyway thanks for suggestion
Marcio