Query below lists schemas in database created by users, i.e. excluding default SYS*, SQLJ, and NULLID schemas.

Use this query to list all schemas.

Query

select schemaname as schema_name,
    owner as schema_owner,
    case ownertype 
        when 'S' then 'system'
        when 'U' then 'individual user' 
    end as schema_owner_type,
    definer as schema_definer,
    case definertype 
        when 'S' then 'system'
        when 'U' then 'individual user'
    end as schema_definer_type
from syscat.schemata    
where schemaname not like 'SYS%'
and  schemaname not IN ('SQLJ', 'NULLID')
order by schema_name

Columns

• schema_name - schema name
• schema_owner - authorization ID of the owner of the schema
• schema_owner_type - owner type:
  • system
  • individual user
• schema_definer - authorization ID of the definer of the schema
• schema_definer_type - definer type
  • system
  • individual user

Rows

• One row represents one schema in a database
• Scope of rows: all schemas in a database, excluding default SYS* , SQLJ, NULLID
• Ordered by schema name

Sample results