All of Your Objects: Reports

Today’s question:
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:

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.’

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.

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:

Object Navigators?

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