DB2 Administrative SQL Cookbook: Listing all Objects in a Schema

Posted by


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.


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


select substr(OWNER,1,12) as OWNER
    , substr(OBJECTNAME,1,30) as OBJECTNAME
with ur;

Sample Output

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

  1 record(s) selected.


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. Required fields are marked *

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