In this article, I'll show you a couple of ways to list all the views in Amazon Athena.

Using SHOW commands

Show commands are excellent for quick investigation but are very limited. They return data only in string format instead of a table.

Using SHOW VIEWS

SHOW VIEWS

You can also use the IN keyword to specify a database name.

SHOW VIEWS IN <DATABASE_NAME>

DESCRIBE VIEW

This command returns column names and types used in a specific view.

DESCRIBE <VIEW_NAME>

Example

DESCRIBE testview

Response

id  varchar
name    varchar
age int

SHOW COLUMNS

Using SHOW COLUMNS you can achieve the same result as with DESCRIBE VIEW.

SHOW COLUMNS <VIEW_NAME>

Example:

SHOW COLUMNS testview

Response:

id  varchar
name    varchar
age int

Querying AWS Glue Data Catalog

Quering AWS Glue Data Catalog you can fetch extended view metadata in a tabular format.

SELECT
    table_catalog AS view_catalog,
    table_schema AS view_database,
    table_name AS view_name,
    view_definition
FROM information_schema.views

Example results:

Dataedo

You can also import AWS Athena views using Dataedo. You can download Dataedo here and proceed with these instructions to import metadata from Athena.

Here's a final result:

You can also explore SQL scripts: