CODEWITHSUNDEEP
sqldatabaseoracle-databaseoracle-sqldeveloper
Rajith Gun Hewage
Rajith Gun Hewage

Reputation: 532

getting the table names and their row counts using a query in oracle db

I have a database and I want to get the names of the tables in that database along with the number of data rows in those tables.

select table_name from dba_tables where owner = 'owner'

returns the names of all the tables in the database, but I can't get the number of rows in each table.

Querying the num_rows column would not give the row count in real time since it requires all the tables to run the "analyze" command on them.

Is there a way to get both columns, the table name and the row count of each respective table using a single query?

Upvotes: 2

Views: 3638

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175646

Use:

SELECT 
   table_name, 
   num_rows,
   last_analyzed
FROM dba_tables

You may need to update statistics before:

exec dbms_stats.gather_schema_stats(ownname => 'NAME');

Upvotes: 3

Related Questions