Snowflake Information Schema

The Snowflake Information Schema (aka “Data Dictionary”) consists of a set of system-defined views and table functions that provide extensive metadata information about the objects created in your account. The Snowflake Information Schema is based on the SQL-92 ANSI Information Schema, but with the addition of views and functions that are specific to Snowflake.

The Information Schema is implemented as a schema named INFORMATION_SCHEMA that Snowflake automatically creates in every database in an account.

Note

ANSI uses the term “catalog” to refer to databases. To maintain compatibility with the standard, the Snowflake Information Schema topics use “catalog” in place of “database” where applicable. For all intents and purposes, the terms are conceptually equivalent and interchangeable.

What is INFORMATION_SCHEMA?

Each database created in your account automatically includes a built-in, read-only schema named INFORMATION_SCHEMA. The schema contains the following objects:

  • Views for all the objects contained in the database, as well as views for account-level objects (i.e. non-database objects such as roles, warehouses, and databases)

  • Table functions for historical and usage data across your account.

Information Schema Views and Table Functions

List of Views

The views in INFORMATION_SCHEMA display metadata about objects defined in the database, as well as metadata for non-database, account-level objects that are common across all databases. Each instance of INFORMATION_SCHEMA includes:

  • ANSI-standard views for the database and account-level objects that are relevant to Snowflake.

  • Snowflake-specific views for the non-standard objects that Snowflake supports (stages, file formats, etc.).

Unless otherwise noted, the Snowflake Information Schema views are ANSI-standard:

View

Type

Snowflake-specific

Notes

APPLICABLE_ROLES

Account

CLASS_INSTANCE_FUNCTIONS

Database

CLASS_INSTANCE_PROCEDURES

Database

CLASS_INSTANCES

Database

CLASSES

Database

COLUMNS

Database

CURRENT_PACKAGES_POLICY

Database

DATABASES

Account

ELEMENT_TYPES

Database

ENABLED_ROLES

Account

EVENT_TABLES

Database

EXTERNAL_TABLES

Database

FIELDS

Database

FILE FORMATS

Database

FUNCTIONS

Database

HYBRID_TABLES

Database

INDEXES

Database

INDEX_COLUMNS

Database

INFORMATION_SCHEMA_CATALOG_NAME

Account

LOAD_HISTORY

Account

Data retained for 14 days.

OBJECT_PRIVILEGES

Account

PACKAGES

Database

PIPES

Database

PROCEDURES

Database

REFERENTIAL_CONSTRAINTS

Database

REPLICATION_DATABASES

Account

REPLICATION_GROUPS

Account

SCHEMATA

Database

SEQUENCES

Database

SERVICES

Database

STAGES

Database

TABLE_CONSTRAINTS

Database

TABLE_PRIVILEGES

Database

TABLE_STORAGE_METRICS

Database

TABLES

Database

Displays tables and views.

USAGE_PRIVILEGES

Database

Displays privileges on sequences only; to view privileges on other types of objects, use OBJECT_PRIVILEGES.

VIEWS

Database

List of Table Functions

The table functions in INFORMATION_SCHEMA can be used to return account-level usage and historical information for storage, warehouses, user logins, and queries:

Table Function

Data Retention

Notes

AUTOMATIC_CLUSTERING_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

AUTO_REFRESH_REGISTRATION_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

COMPLETE_TASK_GRAPHS

60 minutes

Results returned only for the ACCOUNTADMIN role, the task owner (i.e. the role with the OWNERSHIP privilege on the task), or a role with the global MONITOR EXECUTION privilege.

COPY_HISTORY

14 days

Results depend on the privileges assigned to the user’s current role.

CURRENT_TASK_GRAPHS

N/A

Results returned only for the ACCOUNTADMIN role, the task owner (i.e. the role with the OWNERSHIP privilege on the task), or a role with the global MONITOR EXECUTION privilege.

DATA_TRANSFER_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

DATABASE_REFRESH_HISTORY

14 days

Results depend on the privileges assigned to the user’s current role.

DATABASE_REFRESH_PROGRESS , DATABASE_REFRESH_PROGRESS_BY_JOB

14 days

Results depend on the privileges assigned to the user’s current role.

DATABASE_REPLICATION_USAGE_HISTORY

14 days

Results returned only for the ACCOUNTADMIN role.

DATABASE_STORAGE_USAGE_HISTORY

6 months

Results depend on MONITOR USAGE privilege. [1]

DYNAMIC_TABLE_GRAPH_HISTORY

N/A

Results depend on the MONITOR USAGE privilege. [1]

DYNAMIC_TABLE_REFRESH_HISTORY

1 day

Results depend on the MONITOR USAGE privilege. [1]

EXTERNAL_FUNCTIONS_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

EXTERNAL_TABLE_FILES

N/A

Results depend on the privileges assigned to the user’s current role.

EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY

30 days

Results depend on the privileges assigned to the user’s current role.

LOGIN_HISTORY , LOGIN_HISTORY_BY_USER

7 days

Results depend on the privileges assigned to the user’s current role.

MATERIALIZED_VIEW_REFRESH_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

NOTIFICATION_HISTORY

14 days

Results returned only for the ACCOUNTADMIN role, the integration owner (i.e. the role with the OWNERSHIP privilege on the integration) or a role with the USAGE privilege on the integration.

PIPE_USAGE_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

POLICY_REFERENCES

N/A

Results returned only for the ACCOUNTADMIN role.

QUERY_ACCELERATION_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

QUERY_HISTORY , QUERY_HISTORY_BY_*

7 days

Results depend on the privileges assigned to the user’s current role.

REPLICATION_GROUP_REFRESH_HISTORY

14 days

Results are only returned for a role with any privilege on the replication or failover group.

REPLICATION_GROUP_REFRESH_PROGRESS, REPLICATION_GROUP_REFRESH_PROGRESS_BY_JOB

14 days

Results are only returned for a role with any privilege on the replication or failover group.

REPLICATION_GROUP_USAGE_HISTORY

14 days

Results depend on the MONITOR USAGE privilege. [1]

REPLICATION_USAGE_HISTORY

14 days

Results returned only for the ACCOUNTADMIN role.

REST_EVENT_HISTORY

7 days

Results returned only for the ACCOUNTADMIN role.

SEARCH_OPTIMIZATION_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

SERVERLESS_TASK_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

STAGE_DIRECTORY_FILE_REGISTRATION_HISTORY

14 days

Results depend on the privileges assigned to the user’s current role.

STAGE_STORAGE_USAGE_HISTORY

6 months

Results depend on MONITOR USAGE privilege. [1]

TAG_REFERENCES

N/A

Results are only returned for the role that has access to the specified object.

TAG_REFERENCES_ALL_COLUMNS

N/A

Results are only returned for the role that has access to the specified object.

TASK_DEPENDENTS

N/A

Results returned only for the ACCOUNTADMIN role or task owner (role with OWNERSHIP privilege on task).

TASK_HISTORY

7 days

Results returned only for the ACCOUNTADMIN role, the task owner (i.e. the role with the OWNERSHIP privilege on the task), or a role with the global MONITOR EXECUTION privilege.

VALIDATE_PIPE_LOAD

14 days

Results depend on the privileges assigned to the user’s current role.

WAREHOUSE_LOAD_HISTORY

14 days

Results depend on MONITOR USAGE privilege. [1]

WAREHOUSE_METERING_HISTORY

6 months

Results depend on MONITOR USAGE privilege. [1]

[1] Returns results if role has been assigned the MONITOR USAGE global privilege; otherwise, returns results only for the ACCOUNTADMIN role.

General Usage Notes

  • Each INFORMATION_SCHEMA schema is read-only (i.e. the schema, and all the views and table functions in the schema, cannot be modified or dropped).

  • Queries on INFORMATION_SCHEMA views do not guarantee consistency with respect to concurrent DDL. For example, if a set of tables are created while a long-running INFORMATION_SCHEMA query is being executed, the result of the query may include some, none, or all of the tables created.

  • The output of a view or table function depend on the privileges granted to the user’s current role. When querying an INFORMATION_SCHEMA view or table function, only objects for which the current role has been granted access privileges are returned.

  • To prevent performance issues, the following error is returned if the filters specified in an INFORMATION_SCHEMA query are not sufficiently selective:

    Information schema query returned too much data. Please repeat query with more selective predicates.

  • The Snowflake-specific views are subject to change. Avoid selecting all columns from these views. Instead, select the columns that you want. For example, if you want the name column, use SELECT name, rather than SELECT *.

Tip

The Information Schema views are optimized for queries that retrieve a small subset of objects from the dictionary. Whenever possible, maximize the performance of your queries by filtering on schema and object names.

For more usage information and details, see the Snowflake Information Schema blog post.

Considerations for Replacing SHOW Commands with Information Schema Views

The INFORMATION_SCHEMA views provides a SQL interface to the same information provided by the SHOW <objects> commands. You can use the views to replace these commands; however, there are some key differences to consider before switching:

Considerations

SHOW Commands

Information Schema Views

Warehouses

Not required to execute.

Warehouse must be running and currently in use to query the views.

Pattern matching/filtering

Case-insensitive (when filtering using LIKE).

Standard (case-sensitive) SQL semantics. Snowflake automatically converts unquoted, case-insensitive identifiers to uppercase internally, so unquoted object names must be queried in uppercase in the Information Schema views.

Query results

Most SHOW commands limit results to the current schema by default.

Views display all objects in the current/specified database. To query against a particular schema, you must use a filter predicate (e.g. ... WHERE table_schema = CURRENT_SCHEMA()...). Note that Information Schema queries lacking sufficiently selective filters return an error and do not execute (see General Usage Notes in this topic).

Qualifying the Names of Information Schema Views and Table Functions in Queries

When querying an INFORMATION_SCHEMA view or table function, you must use the qualified name of the view/table function or the INFORMATION_SCHEMA schema must be in use for the session.

For example:

  • To query using the fully-qualified names of the view and table function, in the form of database.information_schema.name:

    SELECT table_name, comment FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • To query using the qualified names of the view and table function, in the form of information_schema.name:

    USE DATABASE testdb;
    
    SELECT table_name, comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
    
    Copy
  • To query with the INFORMATION_SCHEMA schema in use for the session:

    USE SCHEMA testdb.INFORMATION_SCHEMA;
    
    SELECT table_name, comment FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ;
    
    SELECT event_timestamp, user_name FROM TABLE(LOGIN_HISTORY( ... ));
    
    Copy

    Note

    If you are using a database that was created from a share and you have selected INFORMATION_SCHEMA as the current schema for the session, the SELECT statement might fail with the following error:

    INFORMATION_SCHEMA does not exist or is not authorized

    If this occurs, select a different schema for the current schema for the session.

For more detailed examples, see the reference documentation for each view/table function.