SHOW TABLES

Shows a list of tables in a schema, along with some table attributes.

Each output row consists of database name, schema name, table name, table type, table ACL, and remarks. For more information about these attributes, see SVV_ALL_TABLES.

If more than 10,000 tables would result from the SHOW TABLES command, then an error is returned.

Syntax

SHOW TABLES FROM SCHEMA database_name.schema_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 AWS 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.

schema_name

The name of the schema that contains the tables to list.

To show AWS Glue Data Catalog tables, provide the AWS Glue database name as the schema name.

filter_pattern

A valid UTF-8 character expression with a pattern to match table 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 tables in the Amazon Redshift database named dev that are in schema public.

SHOW TABLES FROM SCHEMA dev.public;

 database_name | schema_name | table_name | table_type | table_acl | remarks 
---------------+-------------+------------+------------+-----------+---------
 dev           | public      | tb         | TABLE      |           | 
 dev           | public      | tb2        | TABLE      |           | 
 dev           | public      | tb3        | TABLE      |           |

Following example shows the tables in the AWS Glue Data Catalog database named awsdatacatalog that are in schema batman.

SHOW TABLES FROM SCHEMA awsdatacatalog.batman;

 database_name  | schema_name |    table_name    | table_type | table_acl | remarks 
----------------+-------------+------------------+------------+-----------+---------
 awsdatacatalog | batman      | nation           | EXTERNAL   |           | 
 awsdatacatalog | batman      | part             | EXTERNAL   |           | 
 awsdatacatalog | batman      | partsupp         | EXTERNAL   |           | 
 awsdatacatalog | batman      | region           | EXTERNAL   |           | 
 awsdatacatalog | batman      | supplier         | EXTERNAL   |           | 
 awsdatacatalog | batman      | automount_nation | EXTERNAL   |           |