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
– Shivam, Commented Aug 8, 2018 at 15:23

EvilTeach · Accepted Answer · 2018-08-08 16:50:23Z

2

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.

answered Aug 8, 2018 at 16:50

EvilTeach

29k21 gold badges89 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related