SHOW SCHEMAS - Amazon Redshift
SyntaxParametersExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

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 configuration data_catalog_auto_mount is set to true. 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    |            |                 |