1 2 Previous Next 23 Replies Latest reply on Nov 21, 2010 4:54 PM by MichaelS

    count(*) for all tables

    694427
      Hi ,
      I want the Query to get the table name and count(*) display in excel like this.Can i get count(*) from metadata table .Please let me know ??

      ACCT 53
      ACCT_CHEQUE 45
      EMP 50
      DEPT 90
        • 1. Re: count(*) for all tables
          Centinul
          If you are okay with counts being a little off and only accurate as of the last time you collected statistics you could query the TABLES (ALL, DBA, USER) views for the NUMROWS column.

          Otherwise you need to construct SQL to query all your tables.
          • 2. Re: count(*) for all tables
            Karthick2003
            A XML solution.
            SQL>  select table_name,
              2     to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) cnt
              3     from all_tables
              4    where owner = 'SCOTT'
              5      and table_name in ('EMP','DEPT');
            
            TABLE_NAME                            CNT
            ------------------------------ ----------
            EMP                                    14
            DEPT                                    4 
            This one is not mine. This question comes up often in this forum. And once i saw this answer. And i thought its really cool so just saved it in my Google Note Book ;)
            • 3. Re: count(*) for all tables
              694427
              I want sql query only.
              • 4. Re: count(*) for all tables
                BluShadow
                Karthick_Arp wrote:
                A XML solution.

                This one is not mine. This question comes up often in this forum. And once i saw this answer. And i thought its really cool so just saved it in my Google Note Book ;)
                You need to update your google note book. It doesn't take account of Index Organised Tables.

                Based on answer from Laurent Schneider
                http://laurentschneider.com/wordpress/2007/04/how-do-i-store-the-counts-of-all-tables.html
                SQL> select
                  2    table_name,
                  3    to_number(
                  4      extractvalue(
                  5        xmltype(
                  6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
                  7        ,'/ROWSET/ROW/C')) count
                  8  from user_tables
                  9 where iot_type != 'IOT_OVERFLOW';
                
                TABLE_NAME                      COUNT
                ------------------------------ ------
                DEPT                                4
                EMP                                14
                BONUS                               0
                SALGRADE                            5
                Edited by: BluShadow on Jul 8, 2009 12:00 PM
                • 5. Re: count(*) for all tables
                  Centinul
                  user5003725 wrote:
                  I want sql query only.
                  That is a query that was posted............

                  What are you really looking for?
                  • 6. Re: count(*) for all tables
                    Karthick2003
                    Thanks a ton :)

                    Dint know the query was flawed. Have updated My Google notebook ;)
                    • 7. Re: count(*) for all tables
                      694427
                      Is there any way to get the query result without using functions
                      • 8. Re: count(*) for all tables
                        MichaelS
                        Or the much simpler 11g variant:
                        SQL>  select table_name, trim(column_value) cnt from user_tables, xmltable((
                              'count(ora:view("'||table_name||'"))'))
                        where table_name in ('EMP','DEPT')
                        /
                        TABLE_NAME                     CNT       
                        ------------------------------ ----------
                        DEPT                           5         
                        EMP                            14        
                        
                        2 rows selected.
                        • 9. Re: count(*) for all tables
                          Karthick2003
                          user5003725 wrote:
                          Is there any way to get the query result without using functions
                          In straight SQL. Hmmmmm.... I guess no
                          • 10. Re: count(*) for all tables
                            MichaelS
                            where iot_type != 'IOT_OVERFLOW';
                            I'd prefer
                            ... where iot_type != 'IOT_OVERFLOW' or iot_type IS NULL;
                            ;)


                            Don't know how the emp table appears in your query though ....
                            • 11. Re: count(*) for all tables
                              BluShadow
                              michaels2 wrote:
                              Don't know how the emp table appears in your query though ....
                              It's not the SCOTT schema, it's a copy of emp and dept in my own schema for demonstration purposes. ;)
                              • 12. Re: count(*) for all tables
                                MichaelS
                                It's not the SCOTT schema, it's a copy of emp and dept
                                yeah, but with an iot_type of what??
                                • 13. Re: count(*) for all tables
                                  SomeoneElse
                                  Why would you want such a query?

                                  You could try this if you must:
                                  select owner, table_name, num_rows, last_analyzed from all_tables;
                                  This is based on the last time you gathered stats for the tables.
                                  • 14. Re: count(*) for all tables
                                    BluShadow
                                    Ah, I see your point. I must have missed something off in the copy/paste (like the "or IOT_TYPE is null") ;)
                                    1 2 Previous Next