DB2 Administrative SQL Cookbook: Listing all Objects in a Schema

Posted by

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.

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.