The view information_schema.schemata contains all schemas in the database.
SELECT * FROM information_schema.schemata;
SELECT * FROM information_schema.schemata WHERE NOT is_system;
SELECT schema_name, schema_owner, default_character_set_name, is_system FROM information_schema.schemata;
information_schema.schemata
column name | type | remarks |
---|---|---|
catalog_name | VARCHAR | Always NULL |
schema_name | VARCHAR | |
schema_owner | VARCHAR | |
default_character_set_catalog | VARCHAR | Always NULL |
default_character_set_schema | VARCHAR | Always NULL |
default_character_set_name | VARCHAR | Always UTF-8 |
sql_path | VARCHAR | Always NULL |
schema_id | INTEGER | reference to sys.schemas.id |
is_system | BOOLEAN | |
comments | VARCHAR |
Note: The last 3 columns (schema_id, is_system and comments) are extensions to the view as defined by ISO standard. They provide useful information and simplify filtering and joins with system tables/views in sys schema when needed.