Count(*) of all tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Count(*) of all tables

  1. #1
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Does anyone have a script to select table_name and count(*) from all tables?? Thanx.

  2. #2
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    come on Halo, I don't expect this from you...can't you use a dynamic sql here :-)

    Rajeev Suri

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    cant you just analyze the schema or database and check num_rows from dba_tables?

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759

    Talking

    Tell me about it. I'm brain ded today.

  5. #5
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    select 'select ''' || table_name || ''', count(*) from ' || table_name || ';' from user_tables


    Rajeev Suri

  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    Well, I haven't been given system privs on this db and so cannot analyze it.
    -
    RSURI: the script did not work!!

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    It looks fine to me...?

    It should generate a set of statements that can be output to a file and then run.

    What is wrong with it?

    Did they maybe not give you rights to USER_TABLES?

    - Chris

  8. #8
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    I think I messed up one quote; Try this --

    select 'select ''' || table_name || ''',' || ' count(*) from ' || table_name || ';' from user_tables;

    Here is the result --


    SQL> select 'select ''' || table_name || ''',' || ' count(*) from ' || table_name || ';'
    2 from user_tables;

    'SELECT'''||TABLE_NAME||''','||'COUNT(*)FROM'||TABLE_NAME||';'
    --------------------------------------------------------------------------------------
    select 'A1', count(*) from A1;
    select 'COURSES', count(*) from COURSES;
    select 'NODES', count(*) from NODES;
    select 'PROGRESS', count(*) from PROGRESS;
    select 'QUEST_IM_BUFFER_BUSY', count(*) from QUEST_IM_BUFFER_BUSY;
    select 'QUEST_IM_EVENT_CATEGORIES', count(*) from QUEST_IM_EVENT_CATEGORIES;
    select 'QUEST_IM_LOCK_TREE', count(*) from QUEST_IM_LOCK_TREE;
    select 'QUEST_IM_MONITOR_VERSION', count(*) from QUEST_IM_MONITOR_VERSION;
    select 'QUEST_IM_PLAN_TABLE', count(*) from QUEST_IM_PLAN_TABLE;
    select 'T2', count(*) from T2;
    select 'T4', count(*) from T4;
    select 'TABLE_SIZES', count(*) from TABLE_SIZES;
    select 'TEST', count(*) from TEST;
    select 'TEST1', count(*) from TEST1;
    select 'TEST2', count(*) from TEST2;
    select 'TEST3', count(*) from TEST3;
    select 'UPCATS', count(*) from UPCATS;
    select 'UPINT', count(*) from UPINT;
    select 'UPROFS', count(*) from UPROFS;
    select 'USERS', count(*) from USERS;

    20 rows selected.

    SQL>
    Rajeev Suri

  9. #9
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    This is for you Chris...

    user_tables is view accesible to everyone; So even a user with just "create session" privilege should be able to see what tables he owns (he won't own any tables though if all he has is "create session" privilege :-)

    - Rajeev

    Rajeev Suri

  10. #10
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759

    Talking

    I made a mistake. Duh!! It works fine. Thanx Rajeev.

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