On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Saturday, October 25, 2008

Execute Immediate: Count Rows in All Tables

The EXECUTE IMMEDIATE statement allows you to build a statement on-the-fly. This is useful if you don't know the name of a table or the where conditions in advance. In this case, we'll get each table name in the schema and then ask for a count. Since this list will vary from time to time, the Execute Immediate makes sure we get a complete list.
Clear Screen
Prompt This will display the record count for all the tables in a schema.
Prompt Note: This does not display external tables, global temporary tables or tables in the recycle bin.
Prompt ------

Accept Schema Prompt 'Please type schema name: '
Prompt

Set Echo Off
Set Serveroutput On Verify Off

Declare
v_Stmt Varchar2(4000) ;
v_Num_Rows Number ;

Begin
Dbms_Output.Enable(1000000) ;

For Rec In
(
Select Owner, Table_Name
From DBA_Tables dt
Where Owner=Upper('&Schema')
And Table_Name Not Like 'BIN%' /* not in recycle bin */
And Not Exists (Select '1' /* not an external table */
From DBA_External_Tables det
Where dt.Table_Name = det.Table_Name
)
And Temporary = 'N' /* Not a temporary table */
Order by Table_Name
)

Loop
v_Stmt:= 'Select Count(1)' ||
'From ' || Rec.Owner || '.' || Rec.Table_Name ;

Execute Immediate v_Stmt Into v_Num_Rows;

Dbms_Output.Put_Line('# Rows: '||
To_Char(v_Num_Rows,'999,999')
||' - '||
Rec.Table_Name
);
End Loop;
End;
/
Thanks to Rob Balter, DBA at MSA for the core logic.
See Oracle Online Documentation for further detail:
Execute Immediate

No comments:

Post a Comment