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
LAST_DDL	TIMESTAMP_LTZ	Timestamp of the last DDL operation performed on the table or view, including database refreshes for replication (if the refresh changes visible properties for the table/view).
LAST_DDL_BY	TEXT	Username of the user who performed the last DDL operation on the table or view.
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

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'

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

SELECT table_name, last_altered FROM mydatabase.information_schema.external_tables;

Copy