Today’s question:
I am looking for a way to list out all and count all objects by all schema, any idea?

Point, click – pretty easy.

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.

Show me stuff, don’t make me write SQL = our pre-canned reports FTW!

So there’s a few reports of interest here for our questioner:

  1. Object Count by Type
  2. 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.’

This covers most of what our customer is asking for.

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.

We’re going to paste this in just a few seconds.

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,
       COUNT(*) "Object Count"
  FROM sys.all_objects
 WHERE substr(
) != 'BIN$'
   AND substr(
) != 'DR$'
   AND ( :owner IS NULL
    OR instr(
) > 0 )
 GROUP BY owner,

And here’s our new child report SQL:

  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:

reports are your best friend – give them a try!

Object Navigators?

Want to have the object list also include links to actually OPEN the objects? Don’t forget this trick!

Click on the hyperlink to open the object.

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment