In this article, I'll present a couple of ways to list all the database tables in Amazon Athena.

Using SHOW TABLES

This approach is excellent for quick investigation but is very limited. It returns only table names and outputs string instead of a table.

SHOW TABLES IN <database_name>

Example:

SHOW TABLES IN default

Results:

elb_logs
elb_logs_void
s3_addresses_csv
s3_biostats_csv
s3_cities_csv
s3_companies_sorted_csv
s3_event_json
s3_fl_insurance_sample_csv
s3_ford_escort_csv
s3_homes_csv
s3_hurricanes_csv
s3_sacramentocrimejanuary2006_csv
s3_sacramentorealestatetransactions_csv
s3_techcrunchcontinentalusa_csv
s3_world_bank_json
s3_zips_json

You can also specify a regular expression to match only specific tables.

SHOW TABLES IN <database_name> <regexp>

Example:

Show only tables starting with 'dog'.

SHOW TABLES IN default 'elb*'

Results:

elb_logs
elb_logs_void

Using information schema

Querying information schema can provide you with full tables' metadata in a tabular format.

SELECT t.table_name,
       t.table_schema AS database_name,
       t.table_catalog,
       t.table_type
FROM information_schema.tables t
LEFT JOIN information_schema.views v ON t.table_name = v.table_name
AND t.table_schema = v.table_schema
WHERE t.table_schema <> 'information_schema'
  AND t.table_schema = '<database_name>'
  AND v.table_name IS NULL;

Example output:

Using AWS CLI

You can also use the official AWS CLI to obtain a list of Athena tables in a JSON, text, or tabular format.

aws athena list-table-metadata \
    --catalog-name <catalog_name> \
    --database-name <database_name> \
    --max-items 2

Example response

{
    "TableMetadataList": [
        {
            "Name": "country_codes",
            "CreateTime": 1586553454.0,
            "TableType": "EXTERNAL_TABLE",
            "Columns": [
                {
                    "Name": "country",
                    "Type": "string",
                    "Comment": "geo id"
                },
                {
                    "Name": "alpha-2 code",
                    "Type": "string",
                    "Comment": "geo id2"
                },
                {
                    "Name": "alpha-3 code",
                    "Type": "string",
                    "Comment": "state name"
                },
                {
                    "Name": "numeric code",
                    "Type": "bigint",
                    "Comment": ""
                },
                {
                    "Name": "latitude",
                    "Type": "bigint",
                    "Comment": "location (latitude)"
                },
                {
                    "Name": "longitude",
                    "Type": "bigint",
                    "Comment": "location (longitude)"
                }
            ],
            "Parameters": {
                "areColumnsQuoted": "false",
                "classification": "csv",
                "columnsOrdered": "true",
                "delimiter": ",",
                "has_encrypted_data": "false",
                "inputformat": "org.apache.hadoop.mapred.TextInputFormat",
                "location": "s3://awsdoc-example-bucket/csv/countrycode",
                "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat",
                "serde.param.field.delim": ",",
                "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe",
                "skip.header.line.count": "1",
                "typeOfData": "file"
            }
        },
        ...

You can read more on this method in the official docs