Re: How to find out number of rows of all tables in the schema?

Hello,
you can have 2 methods, all logged under sql*plus with the owner of those tables :

1. with generated file containing select count(*) : SQL > set pagesize 0 SQL > set linesize 120 SQL > spool mycount.sql SQL > select 'select ' || '''Number of rows in ' || table_name || ' is : ''' || ',count(*) from ' || table_name || ';' from user_tables; SQL > spool off SQL > @mycount.sql You will get something like this : Number of rows in : TABLE1 is 14000 Number of rows in : TABLE2 is 500 etc....
2. using analyze command to calculate statistics on your table and get the number of rows in column num_rows of user_tables (or dba_tables) view: SQL > set pagesize 0 SQL > set linesize 120 SQL > spool analyze.sql SQL > select 'analyze table ' || table_name || 'compute statistics;' from user_tables; SQL > spool off SQL > @analyze.sql At this step all your table (of the owner under you are logged) are analyzed To get number of rows, get num_rows from user_tables SQL > select 'Number of rows in ' || table_name || ' is : ' || num_rows from user_tables;

If you work without optimizer then to delete statistics do the same with 'delete statistics' instead of 'compute statistics'

Hope this help

Regards.
Olivier

suisum_at_freenet.edmonton.ab.ca wrote:

> Hi all:
>
> How can I find out the number of rows of each table in a schema?
>
> Do I need to write a script? Do I need to store the count in a temporary
> table in order to present it nicely in a report?
>
> --
> Best regards,
Received on Wed Sep 02 1998 - 15:22:48 CDT