This discussion is archived
9 Replies Latest reply: Dec 4, 2012 12:56 AM by Purvesh K RSS

Count rows from several tables

936154 Newbie
Currently Being Moderated
hello,

im trying to count row from multiple tables

for example i need the select statement to produce the following

table_name count
table1 5
table2 6
table3 3


i came up with the following script but it counts the number of tables i have

select object_name, (select count(*) from user_tables where table_name = object_name) from all_objects
where object_type = 'TABLE'
  • 1. Re: Count rows from several tables
    908002 Expert
    Currently Being Moderated
    declare
    lcount number;
    begin
    for data in ( select * from user_tables)
    loop
     execute immediate 'select count(*) from '||data.table_name into lcount;
    dbms_output.put_line( data.table_name ||' -------'|| lcount);
    end loop;
    end;
  • 2. Re: Count rows from several tables
    930854 Newbie
    Currently Being Moderated
    hi,

    use this query

    select table_name,num_rows from user_tables

    It ll helpful to you.
  • 3. Re: Count rows from several tables
    ranit B Expert
    Currently Being Moderated
    I guess this's not possible using just a SQL query... let's try Pl/Sql.
    declare
        v_count number := 0;
    begin
        for i in (select owner||'.'||table_name x from all_tables)
        loop
            execute immediate 'select count(1) from '||i.x into v_count;
            dbms_output.put_line(v_count);
        end loop;
    end;
    Ranit B.
  • 4. Re: Count rows from several tables
    930854 Newbie
    Currently Being Moderated
    Hi,

    It's possible to retrieve the records.

    TABLE_NAME NUM_ROWS
    REGIONS     5
    COUNTRIES     5
    LOCATIONS     5
    DEPARTMENT_S     5
    EMPLOYEES     5
    JOBS     5
    JOB_HISTORY     5
    GRP_REP     7
    NUMTABLE     5
    PROMOTIONS     12
    STORE     4
    GEOGRAPHY     4
    XMLTABLE     0
    S1     3
    S2     0
    STUDENT_DETAILS     8
    DEPARTMENT     10
    CONTACT     8
    STUDENT     9
    T1     3
    T2     2
  • 5. Re: Count rows from several tables
    Manik Expert
    Currently Being Moderated
    May be possible:

    Check this:
    SELECT table_name,
           TO_NUMBER (
              EXTRACTVALUE (
                 xmltype (
                    DBMS_XMLGEN.getxml ('select count(*) c from ' || table_name)),
                 '/ROWSET/ROW/C'))
              COUNT
      FROM (select * from all_tables where table_name in ('TABLE1','TABLE2'))
     WHERE owner = 'SCOTT';
    Cheers,
    Manik.
  • 6. Re: Count rows from several tables
    ranit B Expert
    Currently Being Moderated
    Manik wrote:
    May be possible:

    Check this:
    SELECT table_name,
    TO_NUMBER (
    EXTRACTVALUE (
    xmltype (
    DBMS_XMLGEN.getxml ('select count(*) c from ' || table_name)),
    '/ROWSET/ROW/C'))
    COUNT
    FROM (select * from all_tables where table_name in ('TABLE1','TABLE2'))
    WHERE owner = 'SCOTT';
    Cheers,
    Manik.
    Awesome Manik... Just too good. Thanks.
    I wish i could have given you the 'Correct' points. ;-)

    Can you please explain the logic in brief? Will be helpful for everybody to understand...
  • 7. Re: Count rows from several tables
    Manik Expert
    Currently Being Moderated
    Credit should be given to : :D

    Laurent Schneider
    http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html

    Cheers,
    Manik.
  • 8. Re: Count rows from several tables
    936154 Newbie
    Currently Being Moderated
    thanks all

    select table_name,num_rows from user_tables

    "num_rows" that was the missing part
  • 9. Re: Count rows from several tables
    Purvesh K Guru
    Currently Being Moderated
    orvan wrote:
    thanks all

    select table_name,num_rows from user_tables

    "num_rows" that was the missing part
    Just in case you are forgetting something, data of USER_TABLES/Sys views is only modified when you collect stats. If that is what your requirement, then well and good but if you do not collect stats regularly then you might get a wrong data to play with.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points