Re: Oracle10g, Need to find number of rows in each table in a schema

RA wrote:
> Hi Gurus,
>
> I am looking for a stored procedure or package to find total number of
> rows in each table in a schema.
>
> Something like, you can pass the schema name ot the SP or may be simple
> query.
>
> Thank you
> RA
>

I have wrote a small function:

FUNCTION UTIL_COUNTROWS (p_owner VARCHAR2, p_table_name VARCHAR2) RETURN PLS_INTEGER
   AS

     row_count   PLS_INTEGER;
     count_command VARCHAR2(2000);

   BEGIN
     count_command := 'SELECT count(*) FROM "' || p_owner || '"."' || 
p_table_name || '"' ;
     EXECUTE IMMEDIATE count_command INTO row_count;

     RETURN row_count;

select table_name,util_countrows('ADMIN',table_name) from all_tables
where owner = 'ADMIN';

TABLE_NAME UTIL_COUNTROWS('ADMIN',TABLE_NAME)

BAAN_DISKSPACE                 17147 

BAAN_DISKSPACE_XXX             17030 

BAAN_JOBS                      137 

BAAN_JOBS_DETAIL_HIST          62006 

BAAN_JOBS_HIST                 39693 

BAAN_ROWCOUNT                  7369 

BAAN_ROWCOUNT_STRATFORD        136 

TEMP_USERROLES                 1665 

UM2_USER                       840 

UM2_USER_ROLES                 1871 

UM_ORA_BAAN                    885 

UM_TMP                         1445 

12 rows selected

Thomas Kindermann Received on Wed Nov 29 2006 - 10:04:01 CET