SQL or PLSQL program for getting count(*) of tables !!

[Chris Fung]

Hi all,

I am planning to keep all the count(*) figures for all my tables to a statistic table. Any idea for using SQL or PLSQL to do this ??

Cheers,

Chris,

[harry d brown jr]

Is there a reason that you have such a POOR record of assigning points?

This member has assigned points to 93 of 164 responses to his/her questions.

http://forums.itrc.hp.com/cm/TopSolutions/1,,CA721374!1!questions,00.html

live free or die
harry

[Wodisch_1]

Hi Chris,

the basic idea usually is to write a SQL script which creates the SQL script you want, and then start it.
Something like

connect internal
set termout off
set feedback off
spool /tmp/count.sql
select 'select count(*) from ' || TABLE_NAME || ';' "rem cmd"
from DBA_TABLES;
spool off;
start /tmp/count.sql

HTH,
Wodisch

[Brian Crabtree · Accepted solution]

There would be a few ways to do this. A PL/SQL statement would work, however you would have to be able to support it in the future. The following should work (caveat emptor though).
------
declare
cursor tablelist is select table_name from user_tables;
tabname varchar2(40);
sqlstatement varchar2(200);
numcount varchar2(20);
begin
open tablelist;
loop
fetch tablelist into tabname;
exit when tablelist%NOTFOUND;
sqlstatement:='select count(*) from '||tabname;
execute immediate sqlstatement into numcount;
insert into stat_table values (tabname,sysdate,numcount);
end loop;
end;
/
-----------

Brian

[Jeanine Kone]

If you are using the cost based optimizer, you should be gathering statistics regularly. This is one of the values computed (num_rows). For a really easy method - you could always just "insert into my_stat_table select table_name, num_rows from dba_tables). This, of course, assumes you update your statistic every day before you do the insert.

[Chris Fung]

Hey Harry,

Honestly, I am not quite aware to give points in HP forums in my past few years!! However, you can check my recent records, among the recent 6 months+, I always give points to those who answer my questions.

I really appreciate you guys spend time and effort to help me. That's the process for me to grow up, to be mature and to be fair for others.

Please be fair to me and don't just count on me in the "PAST"

Chris,