9 Replies Latest reply: Feb 28, 2008 3:38 PM by 519688 RSS

    How do i store the counts of all tables in my schema

    514114
      Hi,

      I want to find the metrics(number of rows in each table) of all tables in my schema.
      I want to write a pl/sql block or stored when executed brings all metrics.

      For example,

      Dept Table
      deptno count(*)
      10 1
      20 1
      30 1
      40 1

      Emp Table

      deptno count(*)
      10 5
      20 15
      30 25
      40 null

      How do i acheive this type of task?

      Thanks in advance
        • 1. Re: How do i store the counts of all tables in my schema
          SomeoneElse
          If estimates (based on the last time you gathered statistics) are good enough, you can do this...
          SQL> select table_name, num_rows from user_tables;

          TABLE_NAME                                 NUM_ROWS
          ------------------------------ --------------------
          BONUS                                             0
          DEPT                                              4
          EMP                                              14
          PLAN_TABLE
          SALGRADE                                          5
          • 2. Re: How do i store the counts of all tables in my schema
            Laurent Schneider
            XML solution :
            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;
            
            TABLE_NAME                      COUNT
            ------------------------------ ------
            DEPT                                4
            EMP                                14
            BONUS                               0
            SALGRADE                            5
            • 3. Re: How do i store the counts of all tables in my schema
              514114
              Thanks for the reply.

              But i want to find out the count based on group by clause of columns or set of columns and find counts not just the raw counts.

              In my case user_tables does not work.

              each table has a different query with groupings.so what do i do in this case?.

              Thanks
              • 4. Re: How do i store the counts of all tables in my schema
                SomeoneElse
                each table has a different query with groupings.so what do i do in this case?.
                Write a separate query for each table and each grouping you want.
                • 5. Re: How do i store the counts of all tables in my schema
                  Laurent Schneider
                  group by which column? always group by deptno? in this case you can simply add the group by to my string.
                  • 6. Re: How do i store the counts of all tables in my schema
                    APC
                    XML solution :
                    At last, a use for XML in the database! Bravo Laurent!

                    Cheers, APC
                    • 7. Re: How do i store the counts of all tables in my schema
                      258929
                      I want to find the count of records from all different schema's tables in a database. and this query doesn't works here.

                      SELECT
                      table_name,
                      TO_NUMBER(extractvalue(XMLTYPE(dbms_xmlgen.getxml('select count(*) c from '||table_name)),'/ROWSET/ROW/C')) COUNT
                      FROM DBA_TABLES
                      WHERE owner='ACH'
                      ORDER BY 1;

                      Can any buddy help me. is there a way without procedure or function... please help.

                      Regards,
                      RahulC
                      • 8. Re: How do i store the counts of all tables in my schema
                        JustinCave
                        Assuming you have appropriate permissions on those tables, and that you don't have appropriate synonyms in place, you would need to qualify the table_name with the schema name. So
                        'select count(*) c from '||table_name
                        would become
                        'select count(*) c from '||owner||'.'||table_name
                        or
                        'select count(*) c from ach.' || table_name
                        Justin
                        • 9. Re: How do i store the counts of all tables in my schema
                          519688
                          but there's multiple rows coming back per table then
                          simply adding the group by to the getxml() sql yields
                          ORA-19025: EXTRACTVALUE returns value of only one node

                          it would have to be something along these lines
                          select
                            xmlsequence(
                                xmltype(
                                 dbms_xmlgen.getxml('select deptno,  count(*) c from '||table_name||' group by deptno')
                                ).extract('/ROWSET/ROW/*') 
                            )
                          from user_tables
                          where table_name in (select table_name from user_Tab_Columns where column_name='DEPTNO')
                          /
                          XMLSEQUENCE(XMLTYPE(DBMS_XMLGEN.GETXML('SELECTDEPTNO,COUNT(*)CF
                          ---------------------------------------------------------------
                          XMLSEQUENCETYPE(XMLTYPE(<DEPTNO>10</DEPTNO>
                          ), XMLTYPE(<C>1</C>
                          ), XMLTYPE(<DEPTNO>20</DEPTNO>
                          ), XMLTYPE(<C>1</C>
                          ), XMLTYPE(<DEPTNO>30</DEPTNO>
                          ), XMLTYPE(<C>1</C>
                          ), XMLTYPE(<DEPTNO>40</DEPTNO>
                          ), XMLTYPE(<C>1</C>
                          ))
                          
                          XMLSEQUENCETYPE(XMLTYPE(<DEPTNO>10</DEPTNO>
                          ), XMLTYPE(<C>3</C>
                          ), XMLTYPE(<DEPTNO>20</DEPTNO>
                          ), XMLTYPE(<C>5</C>
                          ), XMLTYPE(<DEPTNO>30</DEPTNO>
                          ), XMLTYPE(<C>6</C>
                          ))
                          
                          
                          2 rows selected.