Introduction

Amazon Redshift does not support MySQL-style commands such as SHOW TABLES or DESCRIBE table_name as direct SQL statements. Instead, you query system views and metadata tables to get the same information. Once you know the right views, the Redshift equivalent is straightforward and often more flexible than the shorthand commands from other databases.

List Tables in a Schema

The most portable way is information_schema.tables.

1SELECT table_schema, table_name
2FROM information_schema.tables
3WHERE table_schema = 'public'
4ORDER BY table_name;

This is the closest SQL equivalent to "show me the tables in this schema."

If you want Redshift-specific metadata, svv_tables is also useful:

1SELECT schema, table_name, table_type
2FROM svv_tables
3WHERE schema = 'public'
4ORDER BY table_name;

That can be more convenient when you want a Redshift-oriented view of objects rather than generic ANSI metadata.

Describe the Columns of One Table

To inspect a table’s columns, use information_schema.columns:

1SELECT
2    ordinal_position,
3    column_name,
4    data_type,
5    is_nullable
6FROM information_schema.columns
7WHERE table_schema = 'public'
8  AND table_name = 'orders'
9ORDER BY ordinal_position;

This is the basic "describe table" equivalent and works well when you want column order, data types, and nullability.

Redshift also exposes pg_table_def, which many users like for quick schema inspection:

1SELECT
2    schemaname,
3    tablename,
4    "column",
5    type,
6    encoding,
7    distkey,
8    sortkey,
9    notnull
10FROM pg_table_def
11WHERE schemaname = 'public'
12  AND tablename = 'orders'
13ORDER BY sortkey DESC, "column";

This adds Redshift-specific details such as encoding, distribution key, and sort key behavior.

Check Distribution and Sort Key Details

If you are working with performance tuning, plain column listing is not always enough. Redshift-specific views help you inspect storage-related metadata too.

For example, svv_table_info gives a quick operational summary:

1SELECT
2    schema,
3    "table",
4    diststyle,
5    sortkey1,
6    encoded,
7    size
8FROM svv_table_info
9WHERE schema = 'public'
10  AND "table" = 'orders';

That is not the same as DESCRIBE, but it is often the next thing you actually need after finding the table definition.

If You Use psql or a SQL Client

Some SQL clients provide their own shortcuts, but those are client features, not Redshift SQL.

For example, in psql you might use meta-commands such as:

\dt public.*
\d public.orders

Those can be handy interactively, but they will not work in JDBC, BI tools, or scripts that expect plain SQL. For portable automation, stick to information_schema and Redshift system views.

Choose the Right Metadata Source

A practical rule:

• use information_schema.tables to list tables
• use information_schema.columns to describe columns
• use pg_table_def or svv_table_info when you need Redshift-specific physical details

That combination covers most day-to-day inspection work.

Common Pitfalls

The biggest mistake is assuming SHOW TABLES or DESCRIBE should work just because they exist in other databases. In Redshift, metadata access is query-based.

Another issue is querying the wrong schema. If your table lives outside public, the metadata query may return nothing even though the table exists.

Permissions also matter. Metadata visibility depends on what the current user can see, so a missing result is not always proof that the object does not exist.

Finally, remember that client meta-commands are not SQL. They are convenient interactively, but they are not the right answer for application code or repeatable scripts.

Summary

• Redshift does not provide direct SQL commands named SHOW TABLES or DESCRIBE.
• Use information_schema.tables to list tables.
• Use information_schema.columns to inspect a table’s columns.
• Use pg_table_def and svv_table_info for Redshift-specific schema and storage details.
• Prefer plain SQL metadata queries over client-only shortcuts when you need portable scripts.