DB2 Administrative SQL Cookbook: Listing all Objects in a Schema

Purpose

To generate a list of objects in a specific schema. Useful if you are analyzing how schemas are used, who uses them, or preparing to drop a schema.

Version

The administrative view used here was introduced in DB2 9.5. Should work on 9.5 and up. Tested on 10.5.

Statement

select substr(OWNER,1,12) as OWNER
    , OWNERTYPE
    , substr(OBJECTNAME,1,30) as OBJECTNAME
    , OBJECTTYPE 
from SYSIBMADM.OBJECTOWNERS 
where OBJECTSCHEMA='SSIRS_AGENCY' 
with ur;

Sample Output

OWNER        OWNERTYPE OBJECTNAME                     OBJECTTYPE              
------------ --------- ------------------------------ ------------------------
SYSIBM       S         SSIRS_AGENCY                   SCHEMA                  

  1 record(s) selected.

Modifications

The above lists all objects for one schema, including the schema itself. SQL could be written against this administrative view to see only a specific object type or subset of types, or an in-list could easily be used to see objects in a set of schemas.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *