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