query to count various schema objects per table
I'm looking to run a single query to get a single result set that lists the number of columns, indexes and constraints for each table in my schema. I have the following but want to add
select table_name, count(*) NUM_CONSTRAINTS from all_constraints where owner='MYAUSER' group by table_name;
but not sure how to organise the joins and format the query....any help please?
select coalesce (t1.table_name, t2.table_name) as table_name,
nvl (NUM_COLUMNS, 0) NUM_COLUMNS,
nvl (NUM_INDEXES, 0) NUM_INDEXES
from
(select table_name, count(*) NUM_COLUMNS from all_tab_columns where owner='MYUSER' group by table_name) t1