How to create a table to store list of table names and the table count?

Question

I am trying to create a table that store table names and count of the table.

I already have the list of tables. How to use this list to get all the count for the tables?

The result should be like this:

TABLE_NAME   NUM_ROWS
------------ --------
tableName1   result from select count(*) from tableName1     
...

Any ideas?

Thanks in advance.

Count of tables means total no of table in that list or count of records in that table??? — Shivam, Aug 08 '18 at 15:23

score 2 · Accepted Answer · answered Aug 08 '18 at 16:50

For a Quick and Dirty solution try.

SELECT TABLE_NAME, NUM_ROWS
FROM USER_TABLES

Oracle stores a wealth of metadata information. You can query into the metadata to get a reasonable estimate. The actual number of rows can be off depending on when the table was last analyzed and how often the table is updated..

If that is not a viable option, you will need to write a pl/sql procedure to loop through the tables (querying USER_TABLES) and using something like EXECUTE IMMEDIATE to select the count of the number of rows on that table.

How to create a table to store list of table names and the table count?

1 Answers1