Forum Stats

  • 3,757,572 Users
  • 2,251,247 Discussions
  • 7,869,867 Comments

Discussions

count(*) for all tables

694427
694427 Member Posts: 101
edited Nov 21, 2010 11:54AM in SQL & PL/SQL
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
«134

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Jul 8, 2009 6:56AM
    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.
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Jul 8, 2009 6:57AM
    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 ;)
  • 694427
    694427 Member Posts: 101
    I want sql query only.
  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond
    edited Jul 8, 2009 7:00AM
    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
  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    user5003725 wrote:
    I want sql query only.
    That is a query that was posted............

    What are you really looking for?
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Thanks a ton :)

    Dint know the query was flawed. Have updated My Google notebook ;)
  • 694427
    694427 Member Posts: 101
    Is there any way to get the query result without using functions
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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.
  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Jul 8, 2009 7:22AM
    user5003725 wrote:
    Is there any way to get the query result without using functions
    In straight SQL. Hmmmmm.... I guess no
  • MichaelS
    MichaelS Member Posts: 8,424 Bronze Crown
    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 ....
This discussion has been closed.