Home » How to check if an external table exists in Redshift

# How to check if an external table exists in Redshift

Sometimes you just want to know if a particular external table or schema exists in Amazon Redshift (Spectrum). This article outlines various alternatives to achieve that.

First, if you’d like to list the tables that match your criteria, you can do that by querying SVV_EXTERNAL_TABLES.

SELECT
*
FROM
SVV_EXTERNAL_TABLES

This table has some interesting columns that you can use to discover if a particular schema or table exists.

• schemaname: name of the schema
• tablename: name of the table
• location: location of the underlying files on the S3 bucket

In the following code, we filter on the schema name, table name, and even the location of the underlying files. Adjust to your use case.

SELECT
*
FROM
SVV_EXTERNAL_TABLES
WHERE
location LIKE '%<YOUR_S3_BUCKET_PREFIX>%' -- Filter on bucket prefix
AND schemaname = '<YOUR_SCHEMA_NAME>' -- Filter on schema name
AND tablename = '<YOUR_TABLE_NAME>' -- Filter on table name

If you’re simply looking for a true/false, instead of the rows that match your criteria, you can wrap everything in an EXISTS or evaluate the COUNT of the rows.

/* Option 1: Evaluate the row count: */
SELECT COUNT(*) > 0 FROM (
SELECT
*
FROM
SVV_EXTERNAL_TABLES
WHERE
tablename = '<YOUR_TABLE_NAME>'
);

/* Option 2: use an EXISTS condition: */
SELECT EXISTS (
SELECT
*
FROM
SVV_EXTERNAL_TABLES
WHERE
tablename = '<YOUR_TABLE_NAME>'
)

There we go. A quick way to check if an external table exists in redshift.

### Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.