Recently a client wanted all the row counts from specific tables on a given scheme. The list of tables to have their row counts documented was at about 50, which accounted for about 90% of the table objects in the scheme. At first I was going to use the “Update Row Count” feature in OBI as this is where the project was grounded. Then, I thought there had to be an easier approach. Using the script below I was able to get the counts and simply eliminate the objects that where in the not needed 10%. The database we were using is Oracle so the PL/SQL won’t work for MS SQL Server. I will try to find the old SQL Server code I used to do this on a previous project and post it here as well at a later time.
set serveroutput on
declare
row_cnt number;
begin
for x in (select table_name
from user_tables
order by table_name) loop
execute immediate
'select count(*) from '
||x.table_name into row_cnt;
dbms_output.put_line(rpad(x.table_name,30)||lpad(to_char(row_cnt),7));
end loop;
end;
/
a variation of this is:
Set heading off
Set feedback off
Set pagesize 0
Set termout off
Set trimout on
Set trimspool on
Set recsep off
Set linesize 100
Column d noprint new_value date_
Column u noprint new_value user_
Spool tmp
Select 'Select '''||table_name||' : ''||count(*) from '||table_name||';',
to_char(sysdate, 'YYYYMMDDHH24MISS') d, user u
from user_tables
order by table_name
/
Spool off
Spool count_&user_._&date_
@tmp.LST
Spool off
References:
http://vivekagarwal.wordpress.com/2007/07/17/how-to-determine-row-count-for-all-tables-in-an-oracle-schema/
http://www.tek-tips.com/viewthread.cfm?qid=1393349&page=23
