sql – How to count the number of rows of all tables in a schema?


I need to export the records of some tables of the same schema in , but due to limitations on the machine from which I am going to carry out this process, I need to know the number of rows of the tables of said schema to decide if I do it like this or I use another alternative to copy the records between one environment and another.

How can I quickly and effectively determine the number of records for all tables in the schema?


The easiest way to achieve it, that is, without procedures or complex things, is to run the following query:

SELECT owner, table_name, num_rows, sample_size, last_analyzed 
FROM all_tables
WHERE owner = 'nombre_de_tu_esquema';

The num_rows column contains the number of rows or records in each of the tables.


As @jpertejo highlights:

Keep in mind that the NUM_ROWS column of the ALL_TABLES view obtains the information from the table's statistics. If the statistics are not up-to-date, it may be that the number of rows returned by the NUM_ROWS does not correspond to the real one. To know exactly the real number of rows, you would have to do a COUNT(*) . The query for the NUM_ROWS is faster than the COUNT(*) , but it may not give correct data in some cases.

Hence, the last_analyzed column is included in the query, the last_analyzed which is to inform the date on which the most recent statistical analysis was made for each table.

Scroll to Top