1

The below query 

select num_rows from user_tables;

in SQL not returning the rows count ,showing as empty like below even though i have two rows in one of user table .

SQL> select num_rows from user_tables;

  NUM_ROWS
----------

can any one help me?

user3701757
  • 41
  • 1
  • 3

1 Answers1

1

Try :

select COUNT(*) from user_tables;

OR 

Select COUNT(table_name) from user_tables

num_rows returns no.of rows value is updated by DBMS_STATS. So it does not contain the current number of rows in the table but an approximation calculated the last time DBMS_STATS was run.

Update : For counting all the rows in all tables in database:

1. Better way:

`select table_name, num_rows from user_tables;`

OR Alternatively

2. using function(not recommended)

create or replace
function get_rows( p_tname in varchar2 ) return number
as
    l_columnValue    number default NULL;
begin
    execute immediate
       'select count(*)
          from ' || p_tname INTO l_columnValue;

    return l_columnValue;
end;

For running function after creating using above code:

 select user, table_name,
           get_rows( user||'.'||table_name) cnt
      from user_tables

Please note this is for oracle. For Sql server refer : How to fetch the row count for all tables in a SQL SERVER database

Community
  • 1
  • 1
Pranav Singh
  • 17,079
  • 30
  • 77
  • 104
  • select COUNT(*) from user_tables; will return the count of tables in user_tables, not returning the row count. – user3701757 Jun 03 '14 at 06:04
  • `COUNT(*)` returns row count for the table. What do you mean by `count of tables in user_tables`? Is `user_tables` is database ???, then `select anything from user_tables` mustn't work – Pranav Singh Jun 03 '14 at 06:10
  • Count(*) of user_tables not returning row count for me. If i try select table_name from user_tables, it showing the all user tables list. So, count(*) of user_tables is not for returning row count of all tables of user_tables. – user3701757 Jun 03 '14 at 06:27
  • Count(*) must work. Anyways try `Select COUNT(table_name) from user_tables` – Pranav Singh Jun 03 '14 at 06:35
  • I tried , but it returning the count of tables , not the rows. See the queires i ran and output. SQL> select * from cell; ID NAME CRC ---------- -------------------------------- ---------- 1 test1 234 2 test2 235 SQL> Select COUNT(table_name) from user_tables; COUNT(TABLE_NAME) ----------------- 3 – user3701757 Jun 03 '14 at 06:50
  • Actually, i have 3 tables, and in one table called cell having only two rows. But whatever query you suggested returning the table count not the row count. – user3701757 Jun 03 '14 at 06:51
  • for which table you want count, your question seems to be unclear? For `cell`, its like `select count(*) from cell`. – Pranav Singh Jun 03 '14 at 07:31
  • No. I want count of all rows of all tables in the user_tables list – user3701757 Jun 03 '14 at 09:57
  • Got it, you want count of all of the tables in the database. Am I correct? Updating answer for that – Pranav Singh Jun 03 '14 at 10:10
  • SQL> select * from cell; ID NAME CRC ---------- -------------------------------- ---------- 1 test1 234 2 test2 235 SQL> select table_name, num_rows from user_tables; TABLE_NAME NUM_ROWS ------------------------------ ---------- PUBSMARKERGENE POLYAPREDICT CELL – user3701757 Jun 03 '14 at 10:51
  • I used above query you suggested. But nothing it showing the above ouput i am getting – user3701757 Jun 03 '14 at 10:53