9 Replies Latest reply: Dec 4, 2012 2:56 AM by Purvesh K RSS

    Count rows from several tables

    936154
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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.