SQLDeveloper: Finding the total amount of rows within a database?

Question

Within my database I would like to know the total amount of rows.

I am able to find out the amount of rows in a certain table in my database with this query:

select count (*) From TABLE_NAME;

However, is there a more efficient way, rather than repeating this for every table?

this question may help you http://stackoverflow.com/questions/2221555/how-to-fetch-the-row-count-for-all-tables-in-a-sql-server-database — Jovin Thariyath, Jan 14 '16 at 11:45
you can use rownum and order by desc and take the first answer — sagi, Jan 14 '16 at 11:50
Can you please give an example of this? Thanks Im very new to DB work? — java123999, Jan 14 '16 at 11:52
Read this question: http://stackoverflow.com/questions/1443704/query-to-list-number-of-records-in-each-table-in-a-database — Orejano, Jan 14 '16 at 12:02

score 0 · Accepted Answer · answered Jan 14 '16 at 12:06

You may use this pl/sql block to know the total no of rows in all tables.

DECLARE  
   t_name VARCHAR2(100);  
   total_rows NUMBER;  
   t_count NUMBER;  
BEGIN  
  total_rows := 0;  
  t_count := 0;  

  FOR rec IN (SELECT table_name FROM user_tables)  
  LOOP  
    EXECUTE IMMEDIATE 'select count(1) from '||rec.table_name  
    INTO t_count;  

    total_rows := total_rows + t_count;  
  END LOOP;  

  dbms_output.put_line('Total no of Rows: '||total_rows);  
EXCEPTION  
  WHEN OTHERS THEN  
    dbms_output.put_line(SQLERRM);  
END;

SQLDeveloper: Finding the total amount of rows within a database?

1 Answers1