Re: counting rows in all_tables ???????

From: Tomm Carr <tommcatt_at_geocities.com>
Date: 1997/07/23
Message-ID: <33D6D786.1F8@geocities.com>#1/1

Erik Oosterling wrote:
>
> I want to make a query which count all the rows in each table that
> ALL_TABLES returns.
>
> so the following result wiil occur:
>
> Table_name Number_rows
>
> AA_DEFINED_USERS 11
> AA_MODULES 12
> AA_AUTO_BATCH 8
>
> Problely this will be easy but i'm not that good at SQL

Well -- yes, it's easy enough. Create a file called, say, ROWCOUNT.SQL and insert the following code:

   declare

      CURSOR tableX is
         SELECT Table_name
         FROM all_tables ;

      dynCurs      INTEGER ;
      dynDummy     INTEGER ;
      dynAnswer    INTEGER ;
   begin
      dynCurs	:= DBMS_SQL.Open_cursor ;
      DBMS_OUTPUT.Put_Line( RPad( 'Table Name' , 32 ) || 'Row Count' ) ;
      DBMS_OUTPUT.Put_Line( RPad( '-' , 30 , '-' ) || '  ' || RPad( '-'

, 9 ,'-' )) ;         

      FOR tableRec in tableX loop
         DBMS_SQL.Parse( dynCurs , 'select count(*) from ' ||
tableRec.Table_name , DBMS_SQL.Native );
         DBMS_SQL.Define_Column( dynCurs , 1 , dynAnswer );
         dynDummy := DBMS_SQL.Execute_And_Fetch( dynCurs , TRUE );
         DBMS_SQL.Column_Value( dynCurs , 1 , dynAnswer );
         DBMS_OUTPUT.Put_Line( RPad( tableRec.Table_name , 32 ) ||
To_Char( dynAnswer ));
      end loop ;
	
      DBMS_SQL.Close_Cursor( dynCurs );
	

   END; Then from SQL*Plus:

   SQL> @rowcount

You'll get a nicely formated display of exactly what you want.

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"

Received on Wed Jul 23 1997 - 00:00:00 CDT