SHOW SCHEMAS
Shows a list of schemas in a database, along with some schema attributes.
Each output row consists of database name, schema name, schema owner, schema type, schema ACL, source database, and schema option. For more information about these attributes, see SVV_ALL_SCHEMAS.
If more than 10,000 schemas can result from the SHOW SCHEMAS command, then an error is returned.
Syntax
SHOW SCHEMAS FROM DATABASE database_name [LIKE 'filter_pattern'] [LIMIT row_limit ]
Parameters
- database_name
-
The name of the database that contains the tables to list.
To show tables in an Amazon Glue Data Catalog, specify (
awsdatacatalog
) as the database name, and ensure the system configurationdata_catalog_auto_mount
is set totrue
. For more information, see ALTER SYSTEM. - filter_pattern
-
A valid UTF-8 character expression with a pattern to match schema names. The LIKE option performs a case-sensitive match that supports the following pattern-matching metacharacters:
Metacharacter Description %
Matches any sequence of zero or more characters. _
Matches any single character. If filter_pattern does not contain metacharacters, then the pattern only represents the string itself; in that case LIKE acts the same as the equals operator.
- row_limit
-
The maximum number of rows to return. The row_limit can be 0–10,000.
Examples
Following example shows the schemas from the Amazon Redshift database named dev
.
SHOW SCHEMAS FROM DATABASE dev;
database_name | schema_name | schema_owner | schema_type | schema_acl | source_database | schema_option ---------------+----------------------+--------------+-------------+-----------------------------+-----------------+--------------- dev | pg_automv | 1 | local | | | dev | pg_catalog | 1 | local | jpuser=UC/jpuser~=U/jpuser | | dev | public | 1 | local | jpuser=UC/jpuser~=UC/jpuser | | dev | information_schema | 1 | local | jpuser=UC/jpuser~=U/jpuser | | dev | schemad79cd6d93bf043 | 1 | local | | |
Following example shows the schemas in the Amazon Glue Data Catalog database named awsdatacatalog
.
The maximum number of output rows is 5
.
SHOW SCHEMAS FROM DATABASE awsdatacatalog LIMIT 5;
database_name | schema_name | schema_owner | schema_type | schema_acl | source_database | schema_option ----------------+----------------------+--------------+-------------+------------+-----------------+--------------- awsdatacatalog | 000_too_many_glue_db | | EXTERNAL | | | awsdatacatalog | 123_default | | EXTERNAL | | | awsdatacatalog | adhoc | | EXTERNAL | | | awsdatacatalog | all_shapes_10mb | | EXTERNAL | | | awsdatacatalog | all_shapes_1g | | EXTERNAL | | |