Re: PL SQL <table_name> , count(*)

Basically the same thing that others have proposed, does the count(*) rather than doing an analyze.

Declare
Column1 varchar2(50);
Column2 Integer;
sqltext varchar2(500);
c_handle Integer;
bogus Integer;
Cursor MyTables is
 select table_name
  from user_tables
  order by 1;
Begin
-- Construct the sql statement. Notice the use of double-single quotes to get

• the single quote into the resulting string variable value. For Rec in MyTables LOOP sqltext := 'Select ''' || Rec.table_name || ''', count(*) FROM ' || Rec.table_Name;
• This statement just gets a handle to the cursor c_handle := dbms_sql.open_cursor;
• This statement just parses the cursor. In DDL, this is all that is required.

dbms_sql.parse(c_handle, sqltext, DBMS_SQL.NATIVE);
-- This binds the return columns. Is is required for select statements.
dbms_sql.define_column(c_handle, 1, Column1, 50); dbms_sql.define_column(c_handle, 2, Column2);
-- Actually execute the statement, required for select and dml statements
bogus := dbms_sql.execute(c_handle);
LOOP
 begin
-- The loop could be written differently.. this next statement fetches the next

• row. If dbms_sql.fetch_rows(c_handle) = 0 THEN EXIT; ELSE
• These statements pull the data into the local variables from the cursor. dbms_sql.column_value(c_handle, 1, Column1); dbms_sql.column_value(c_handle, 2, Column2); dbms_output.put_line(Column1 || ' ' || to_char(Column2)); End If; exception when others then EXIT; end; End LOOP;
• You need to manually close the cursor dbms_sql.close_cursor(c_handle); end loop; end; /

Matt Gross wrote:

> I would like to create a pl/sql script which gives me a list of all tables
> and then gives me a count of all rows for each table.  Does anyone know how
> to do this?
>
> Thanks,
>
> Matt