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?
set heading off
select 'select '''||table_name||'---''||'||' count(*) from '||table_name||';' from user_tables ;
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.
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> select 'analyze table '||table_name||' compute statistics;' from user_tables;
SQL>select table_name, num_rows from user_tables;
Hope this helps.
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.
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Click Here to Expand Forum to Full Width