EXTERNAL_TABLES View

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

See also:

COLUMNS View , VIEWS View

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

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 table_name, last_altered FROM mydatabase.information_schema.external_tables;
Copy