List schemas in Db2 database

Query below lists all schemas in Db2 database. List includes default SYS* , SQLJ, NULLID schemas.

If you want to list user only schemas use this query.

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
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, including default ones
  • Ordered by schema name

Sample results

Here is a view of database schemas in IBM Data Studio:

0
There are no comments. Click here to write the first comment.