For each view in USER_VIEWS, I would like to return the
name,
date created,
date last modified, and the
number of rows returned by that view.
Here's what I have so far:
col object_name format a20
select
object_name,
created,
last_ddl_time
from user_objects
where object_type = 'VIEW'
Returns:
OBJECT_NAME CREATED LAST_DDL_TIME
-------------------- --------- -------------
AISLE_AVG 11-FEB-12 11-FEB-12
COURSE_AVG 11-FEB-12 20-FEB-12
EXE_12_VIEW 11-FEB-12 21-FEB-12
L1_P2 17-FEB-12 17-FEB-12
L1_P3 17-FEB-12 17-FEB-12
L1_P4 17-FEB-12 17-FEB-12
L1_P5A 17-FEB-12 17-FEB-12
L1_P5B 17-FEB-12 17-FEB-12
LAB3_1A 12-FEB-12 20-FEB-12
LAB3_1B 12-FEB-12 20-FEB-12
LAB3_2A 12-FEB-12 20-FEB-12
LAB3_2B 12-FEB-12 20-FEB-12
LAB5_1 19-FEB-12 19-FEB-12
LAB5_2 19-FEB-12 19-FEB-12
LAB5_3 19-FEB-12 19-FEB-12
LAB5_4 19-FEB-12 19-FEB-12
LAB5_5 19-FEB-12 19-FEB-12
LAB5_TIMES 19-FEB-12 19-FEB-12
LAB6_1 19-FEB-12 19-FEB-12
LAB7_VIEW 20-FEB-12 20-FEB-12
PROGRAMS 11-FEB-12 21-FEB-12
STUDENT_GPA 11-FEB-12 21-FEB-12
22 rows selected
How can I add a final column which counts the number of rows returned by said view?
EDIT - Here's a dump of my
tables, and here are my
views.
EDIT2 - Can this be accomplished using the DECODE function, by any chance?