SHOW COLUMNS - 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 COLUMNS

Shows a list of columns in a table, along with some column attributes.

Each output row consists of a comma-separated list of database name, schema name, table name, column name, ordinal position, column default, is nullable, data type, character maximum length, numeric precision, and remarks. For more information about these attributes, see SVV_ALL_COLUMNS.

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

Syntax

SHOW COLUMNS FROM TABLE database_name.schema_name.table_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.

schema_name

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

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

table_name

The name of the table that contains the columns to list.

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 columns in the Amazon Redshift database named dev that are in schema public and table tb.

SHOW COLUMNS FROM TABLE dev.public.tb;

 database_name | schema_name | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | numeric_precision | remarks 
---------------+-------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------
 dev           | public      | tb         | col         |                1 |                | YES         | integer   |                          |                32 |

Following example shows the tables in the Amazon Glue Data Catalog database named awsdatacatalog that are in schema batman and table nation. Output is limited to 2 rows.

SHOW COLUMNS FROM TABLE awsdatacatalog.batman.nation LIMIT 2;

 database_name  | schema_name | table_name | column_name | ordinal_position | column_default | is_nullable | data_type | character_maximum_length | numeric_precision | remarks 
----------------+-------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+-------------------+---------
 awsdatacatalog | batman      | nation     | n_nationkey |                1 |                |             | integer   |                          |                   | 
 awsdatacatalog | batman      | nation     | n_name      |                2 |                |             | character |                          |                   |