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.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. Ember lives in Denver and work from home

Leave a Reply

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

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