DB2 Administrative SQL Cookbook: Listing all Objects in a Schema


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 Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 552

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.