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.