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.

Required permissions

To view a column in an Amazon Redshift table, the current user must satisfy one of the following criteria:

• Be a superuser.

• Be the owner of the table.

• Granted USAGE privilege on the parent schema and granted SELECT privilege on the table or granted SELECT privilege on the column.

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 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.

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 AWS 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 |                          |                   |