Today’s question:
I am looking for a way to list out all and count all objects by all schema, any idea?
![](https://www.thatjeffsmith.com/wp-content/uploads/2018/10/schema-flat.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2018/10/flat-view.png)
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.’
![](https://www.thatjeffsmith.com/wp-content/uploads/2018/10/object-count-by-type1.png)
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.
![](https://www.thatjeffsmith.com/wp-content/uploads/2018/10/copy-report1.png)
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.
And ta-da!
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:
![](https://www.thatjeffsmith.com/wp-content/uploads/2018/10/objects2.png)
Object Navigators?
Want to have the object list also include links to actually OPEN the objects? Don’t forget this trick!
![](https://www.thatjeffsmith.com/wp-content/uploads/2017/03/scope3.png)