List of external schemas and tables from Amazon Redshift

Question

I have created external schema and external table in Redshift. I'm able to see external schema name in postgresql using \dn.

What will be query to do it so that i can run it in java? and also the query to get list of external table?

I tried

select * from information_schema.tables where 
table_schema not in ('pg_catalog', 'information_schema') 
and table_schema not like 'pg_toast%' 

but it didn't get the list of external tables.

Answer 1

Redshift Spectrum external databases, schemas, and tables have their own catalog views.

• SVV_EXTERNAL_DATABASES
• SVV_EXTERNAL_SCHEMAS
• SVV_EXTERNAL_TABLES
• SVV_EXTERNAL_PARTITIONS
• SVV_EXTERNAL_COLUMNS

You can query these from any connection: SELECT * FROM svv_external_schemas;