sqldatabaseoracle-databaseoracle-sqldeveloper

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?

Solution

  • 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');