Multiple tables from a database

[aksahoo17]

Hi,

I want to know , how to retrive the table name from multiple tables from a database which having the maximum number of rows....

As i am new to oracle can anybody help me as fast as possible...

Thanks

Ashish

[urtrivedi]

select TABLE_NAME, NUM_ROWS from all_tables where owner='SCHEMANAME' ORDER BY num_rows DESC

[aksahoo17]

Thanks for reply , but can u plz explain wht is wht in this query so that it better to write ....
Please

[urtrivedi]

SELECT TABLE_NAME, NUM_ROWS FROM all_tables WHERE owner='SCHEMANAME' ORDER BY num_rows DESC

1) ALL_TABLES is oracle system table
2) If your username IN oracle AAKASH, then here owner or shchem is aaksas in CAPITAL letters, it will show only tables belong to AAKASH oracle user.
3) there are so many properties of table, you may use * in query. NUM_ROWS is no of records in that table. And I have ordered it in descending manner by number of records
4) So when u see query result, you will find table with most no of rows, at the top.

[aksahoo17]

Thanks

Sorry i want just one more thing ...
I need ,all the datas from the table(one table) having more number of rows from multiple tables within a database.

[urtrivedi]

It can not be done through pure sql, I guess you want show content of table which is having max number of rows in database. So here your table name is not know to query.

What is your front end. If you can build query dynamically. Then you have to execute two queries one by one.

FIrst one I given you in above post, NOw the top most rows TABLE_NAME is your required table name

Now you can build another query

select * from 'tablename'

I am not sure I am able to explain or not.