Updated on 2022-11-18 GMT+08:00

SHOW SCHEMAS (DATABASES)

Syntax

SHOW SCHEMAS|DATABASES [ (FROM| IN) catalog ] [ LIKE pattern [ESCAPE escapeChar]]

Description

In this statement, DATABASES and SCHEMAS are conceptually equivalent and interchangeable. This statement is used to list all schemas defined in MetaStore. The optional clause LIKE can use rule operations to filter results. It supports the wildcards '*' (matching any character) and '|' (matching optional items).

Example

List all schemas of the current catalog:
SHOW SCHEMAS;

List all schemas whose schema name prefix is t in a specified catalog:

SHOW SCHEMAS FROM hive LIKE 't%';

--Equivalent writing:
SHOW SCHEMAS IN hive LIKE 't%';

If a character in the matching character string conflicts with the wildcard, you can specify an escape character to identify the character. For example, to query all schemas whose schema name prefix is pm_ in the hive catalog, set the escape character to /.

SHOW SCHEMAS IN hive LIKE 'pm/_%' ESCAPE '/';