DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Help with counting records in atable

  1. #1
    Join Date
    Jan 2007
    Posts
    5

    Help with counting records in atable

    Hi,

    I have created around 40 tables in a database. Is there a way I can count total records in all the tables at a time instead of doing table by table?

    Your help is appreciated.

    Thanks,

  2. #2
    Join Date
    Jun 2006
    Location
    Chennai, INDIA
    Posts
    72
    Hi,
    You can analyze those tables and select num_rows from dba_tables.

  3. #3
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    That however will return the number of rows at the moment when the tables are analyzed, not the actual number of rows.
    Not to mention that the number might be inconsistent, since the number of rows of table 1 will be counted at one moment and in table 2 in another
    What about

    select (select count(*) from employees)+(select count(*) from departments
    from dual

    That is doing again counting table by table, but the result is aggregated by the outer select

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Bore
    select (select count(*) from employees)+(select count(*) from departments
    from dual
    One empty table will spoil that for you -- use NVL(count(*),0).

    you could also ...

    select count(*) from
    (
    select null from employees
    union all
    select null from departments
    union all
    ...
    )
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by ujjwala
    Hi,

    I have created around 40 tables in a database. Is there a way I can count total records in all the tables at a time instead of doing table by table?

    Your help is appreciated.

    Thanks,
    Only 40 tables? If there's not too many rows (ie) not 100,000,000 then do a quick Export and the logfile will show how many rows in each table.

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Dave,
    count(*) from empty table is still 0 not null. It is good to think about nulls in arithmetic expressions but no need of nvl here

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width