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.