SQL Language (MOSC)

MOSC Banner

query to count various schema objects per table

edited Feb 19, 2019 1:16PM in SQL Language (MOSC) 3 commentsAnswered

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center