SHOW SCHEMAS

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Lists the schemas that match an optionally supplied regular expression pattern. If no pattern is supplied then the command lists all the schemas in the catalog.

While usage of SCHEMAS and DATABASES is interchangeable, SCHEMAS is preferred.

Syntax 

SHOW SCHEMAS [ { FROM | IN } catalog_name ] [ [ LIKE ] regex_pattern ]

Parameters

  • catalog_name

    Optionally the name of the catalog for which to show schemas. If no catalog is specified the current catalog applies.

  • regex_pattern

    A regular expression pattern that is used to filter the results of the statement. - Except for * and | character, the pattern works like a regular expression. - * alone matches 0 or more characters and | is used to separate multiple different regular expressions, any of which can match. - The leading and trailing blanks are trimmed in the input pattern before processing. The pattern match is case-insensitive.

Examples 

-- Create schema. Assumes a schema named `default` already exists in
-- the system.
> CREATE SCHEMA payroll_sc;
> CREATE SCHEMA payments_sc;

-- Lists all the schemas.
> SHOW SCHEMAS;
 databaseName
 ------------
      default
  payments_sc
   payroll_sc

-- Lists schemas with name starting with string pattern `pay`
> SHOW SCHEMAS LIKE 'pay*';
 databaseName
 ------------
  payments_sc
   payroll_sc

-- Lists all schemas. Keywords SCHEMAS and DATABASES are interchangeable.
> SHOW SCHEMAS;
 databaseName
 ------------
      default
  payments_sc
   payroll_sc

-- Lists all schemas in catalog `some_catalog`.
> SHOW SCHEMAS IN some_catalog;
 databaseName
 ------------
      schema1
      schema2