0

The title says it all. I don't have too much experience in PLSQL and need to figure out how to display a list of tables, their column count and the row count. It'll eventually be a view, but if you guys could at least point me towards the right direction with a single SELECT statement that does the job, I'd be happy! Thanks!

Michal Rogozinski
  • 1,713
  • 1
  • 17
  • 23
  • You might want to take a look at this to start: http://stackoverflow.com/questions/2247310/how-do-i-list-all-tables-in-a-schema-in-oracle-sql – shree.pat18 Apr 13 '14 at 17:10
  • I saw this one, but it only lists the columns, I'd need both the column count and the row count in one query along with the table name. I keep playing with this one. Thanks anyways! – Michal Rogozinski Apr 13 '14 at 17:12

1 Answers1

2

Try this. Replace "all_" with "dba_" if you have sufficient privileges in the database. The all_ prefix will show those objects to which the current user has access only. The dba_ ones show everything, but you need the privilege to access them.

select x.table_name, x.num_cols, y.num_rows
  from (select table_name, count(*) as num_cols
          from all_tab_cols
         group by table_name) x
  join (select table_name, num_rows from all_tables) y
    on x.table_name = y.table_name

As a side note, w/ respect to the number of rows, this will show the number of rows on the table for the last time the table was analyzed. The statistics should be kept up to date especially for performance but you should be aware that the count is not 'live'. If you want to update the statistics for a table this would be how to do so:

analyze table table_name compute statistics;

You can run the below to see when each table was last analyzed (the query I gave you will essentially show the # of rows on each table as of that date.

select owner, table_name, last_analyzed from all_tables order by 3, 1, 2;
Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Unfortunately I'm getting an error in line 2: ORA-00937: not a single-group group function – Michal Rogozinski Apr 13 '14 at 17:17
  • I forgot the group by in the initial post. Try it w/ the edit as it is now. – Brian DeMilia Apr 13 '14 at 17:18
  • @MichalRogozinski: the row count returned by this query is **not** an exact row count of the table. The `num_rows` stored in `all_tables` is only an *approximate* value (based on the last time the table was analyzed) –  Apr 13 '14 at 17:22
  • Ugh... is there a way of getting an actual rowcount on each of the tables listed? All in one query? – Michal Rogozinski Apr 13 '14 at 17:25
  • @MichalRogozinski see my edit. Using the database statistics is the best way as it would not be practical to count the rows of every single table in the database. The statistics should be relatively up to date. If you want to update the statistics for any tables you can use the query I put in the edit. – Brian DeMilia Apr 13 '14 at 17:26