Identify a table with maximum rows in Oracle

Question

I have a set of tables in Oracle and I would like to identify the table that contains the maximum number of rows.

So if, A has 200 rows, B has 345 rows and C has 120 rows I want to be able to identify table B.

Is there a simple query I can run to achieve this?

Edit: There are 100 + tables so I am looking for something generic.

Answer 1

Given that you said you were using Oracle I would just query the meta-data.

select table_name, max(num_rows) from all_tables where table_name in ('A', 'B', 'C');

Just saw your edit. Just run the above without the where clause and it will return the largest table in the database. Only problem may be that you might get a SYS$ table or something. Alternately if you are just doing this for your own knowledge just do

select table_name, num_rows from all_tables order by num_rows; 

and you'll see what the biggest are.

Answer 2

The table in your schema that has max rows:

with data as 
(
 select table_name,
        to_number(extractvalue(xmltype(
                  dbms_xmlgen.getxml (
                 ' select count(*) c from ' || table_name)),
                  '/ROWSET/ROW/C')) countrows
 from   user_tables
)
select table_name, countrows
from   data 
where  countrows = (select max(countrows)
                    from   data);

dbms_xmlgen.getxml(' select .... ') is extremely flexible.