Rows per table

[Trina]

Hi everyone,

I have an OWNER that has 20 tables. I want to write the script that can show
the total number of rows per table. I don't want to enter the table name manually.
How can I do that?

Thanks,

Trina

[sree_sri]

set heading off
spool tab_row_count.sql
select 'select '''||table_name||'---''||'||' count(*) from '||table_name||';' from user_tables ;
spool off
spool tab_row_result.txt
@tab_row_count.sql
spool off

If you run these command from owner account then you will get all that tables total rows count will retrieve into tab_row_result.txt. The format will come like this.
(TABLE_NAME---ROW COUNT)
EMP---10
DEPT---20

Good Luck

[SANJAY_G]

If the tables are big in size, selecting count(*) will take long time. Better analyze all tables and then select num_rows from user_tables.

SQL>spool analyze.sql;
SQL> select 'analyze table '||table_name||' compute statistics;' from user_tables;
SQL>spool off;
SQL>@analyze.sql
SQL>select table_name, num_rows from user_tables;

Hope this helps.

Sanjay

[jmodic]

Sanjay's suggestion is valid, except for one thing. He suggests using analyze in case tables are big, implying that computing statistics will be quicker than COUNT(*). Obviously both options will perform full table scan on the table, but in addition to that analze has to perform some additional internal tasks to get all the statistics, which means that ANALYZE will take more time to complete, not less.