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.
