EXTERNAL_TABLES View¶

This Information Schema view displays a row for each external table in the specified (or current) database.

Columns¶

Column Name

Data Type

Description

TABLE_CATALOG

TEXT

Database that the table belongs to

TABLE_SCHEMA

TEXT

Schema that the table belongs to

TABLE_NAME

TEXT

Name of the table

TABLE_OWNER

TEXT

Name of the role that owns the table

CREATED

TIMESTAMP_LTZ

Creation time of the table

LAST_ALTERED

TIMESTAMP_LTZ

Last altered time of the table

AUTO_CLUSTERING_ON

BOOLEAN

Whether automatic clustering is enabled for the table

COMMENT

TEXT

Comment for this table

LOCATION

TEXT

External stage where the files containing data to be read are staged

FILE_FORMAT_NAME

TEXT

Named file format that describes the staged data files to scan when querying the external table

FILE_FORMAT_TYPE

TEXT

Format type of the staged data files to scan when querying the external table

Usage Notes¶

• The view only displays objects for which the current role for the session has been granted access privileges. The view does not honor the MANAGE GRANTS privilege and consequently may show less information compared to a SHOW command when both are executed by a user who holds the MANAGE GRANTS privilege.

• The view does not include external tables that have been dropped.

• To view only external tables in your queries, filter using a WHERE clause, e.g.:

... WHERE table_schema != 'INFORMATION_SCHEMA'

Examples¶

Retrieve the list of all external tables in all schemas in the mydatabase database:

SELECT * FROM mydatabase.information_schema.external_tables;