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

Question:

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?

Answer:

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.


Upgrade:

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