Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    2

    Unanswered: count records in multiple tables

    Hi All,
    didn't find this one looking through the archives, so here goes:

    (this is Oracle, btw)
    I'd like to get the record counts for multiple tables that follow the same naming convention, say "T%".

    I know how to get the table names - select table_name from dba_tables where table_name like "T%";

    How can I now get a record count for each of those tables, to result in a list like :

    table_name count
    ----------- ------
    T001 0
    T002 5
    T002B 50
    T003 9

    etc ?

    TIA,

    Pete

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    If the tables have been analysed you can get this information from dba_tables.

    select table_name, num_rows from dba_tables where table_name like "T%";

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But, if you don't have access to DBA_TABLES, you could use this procedure:
    PHP Code:
    CREATE OR REPLACE PROCEDURE TabCount (par_tname IN VARCHAR2IS 
    /* purpose: select count(*) from table_name
         usage : EXEC tabcount('')     - all tables
                 EXEC tabcount('emp') - table name like 'EMP%'
    */ 
    CURSOR tab_cur (par_tname TAB.tname%TYPE)
    IS
         SELECT tname FROM TAB
         WHERE tname LIKE UPPER
    (par_tname) || '%' 
         
    ORDER BY tname;
    retval NUMBER;
     
    BEGIN
     
    FOR tab_rec IN tab_cur (par_tnameLOOP
       EXECUTE IMMEDIATE
          
    'select count(*) from ' || tab_rec.tname
          INTO retval
    ;
       
    dbms_output.put_line(tab_rec.tname || ' - ' || retval);
    END LOOP
     
    END TabCount
    Last edited by Littlefoot; 05-28-04 at 07:40.

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    If logged in user is the owner, what gannet gave as an answer is also available through the USER_TABLES view...

    JoeB

  5. #5
    Join Date
    May 2004
    Posts
    2
    Thanks, I'll give that a try.
    -Pete

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    This sql will give you a result per table ... run the sql, then cut and paste the results ...

    sql> select 'select count(*) '||table_name||' from '||table_name||';'
    from user_tables where table_name like 'T%';

    HTH
    Gregg

Posting Permissions

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