Rows per table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Rows per table

  1. #1
    Join Date
    Oct 2000
    Posts
    144

    Question

    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

  2. #2
    Join Date
    Feb 2001
    Posts
    203
    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
    sree

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width