I am looking for a way to list out all and count all objects by all schema, any idea?
So the connection tree is nice in that it makes it easy to see specific types of objects by schema – but if you want a FLAT view of a schema, it’s not so great.
Ok, so what’s a SQL Developer user to do?
Try our Data Dictionary Reports
The Reports panel is there, you just need to click into it.
So there’s a few reports of interest here for our questioner:
- Object Count by Type
- All Objects
Both of these reports support SCHEMA filtering – so by default we’ll show you everything for every schema, but you can also say, ‘Hey, just show me what’s what in HR.’
But, they also want to see the list of objects.
We could take a look at the ‘All Objects’ report. But, we could also CUSTOMIZE the Object Counts by Type report. Let’s do THAT.
Select the report. Right-click.
So we can’t change these supplied reports. But, we can copy them to a ‘User Defined Report,’ and make it do anything we want. So let’s Paste this in the User Defined section.
Now that it’s a user-defined report, I can customize it.
I’m going to add a child report called ‘Objects’, which selects based on object type and owner using the :BIND trick we’ve discussed earlier.
Here’s our PARENT SQL –
SELECT owner, object_type, COUNT(*) "Object Count" FROM sys.all_objects WHERE substr( object_name, 1, 4 ) != 'BIN$' AND substr( object_name, 1, 3 ) != 'DR$' AND ( :owner IS NULL OR instr( UPPER(owner), UPPER( :owner ) ) > 0 ) GROUP BY owner, object_type ORDER BY 3 DESC
And here’s our new child report SQL:
SELECT * FROM all_objects WHERE owner = :OWNER AND object_type = :OBJECT_TYPE ORDER BY object_name ASC
Let’s run the report, select an item type, and see what’s what:
Want to have the object list also include links to actually OPEN the objects? Don’t forget this trick!