Re: Help with a SQL statement

From: Ed Bruce <Ed.Bruce_at_ha.hac.com>
Date: Tue, 15 Sep 1998 16:55:33 -0500
Message-ID: <35FEE255.88F8A2E1@ha.hac.com>

stuco_at_mailcity.com wrote:
>
> I would like to issue one SQL or PL/SQL statement that would gather
> table_names and their respective row counts. How can this be done?? I know
> that I can write a SQL statement to create a bunch of SQL calls, but I would
> rather only make one call to the database to save on overhead.
>
> thanks ever so much!

Write a stored procedure as follows:

create or replace procedure My_Table_Count as

   Select_Table VARCHAR2(100) := 'select count(*) from ';    Count_Of_Rows INTEGER;
   First_Cursor INTEGER default DBMS_SQL.Open_Cursor;    Rows_Processed INTEGER;
begin

   for i in (select table_name from user_tables) loop

      DBMS_SQL.Parse(First_Cursor,
                     Select_Table || i.table_name,
                     DBMS_SQL.Native);
      DBMS_SQL.Define_Column(First_Cursor, 1, Count_Of_Rows);
      Rows_Processed := DBMS_SQL.Execute_And_Fetch(First_Cursor);
      DBMS_SQL.Column_Value(First_Cursor, 1, Count_Of_Rows);

      DBMS_Output.Put_Line('Table: ' || i.table_name || ' Rows: ' ||
                           Count_Of_Rows);

   end loop;
end; Received on Tue Sep 15 1998 - 16:55:33 CDT