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.
In this Topic:
- What is the INFORMATION_SCHEMA Schema?
- Information Schema Views and Table Functions
- General Usage Notes
- Considerations for Replacing SHOW Commands with Information Schema Views
- Querying Information Schema Views and Table Functions
The ANSI Information Schema 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 the INFORMATION_SCHEMA 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:
List of Table Functions¶
The table functions in the 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|
|COPY_HISTORY||Last 7 days||Results returned only for the ACCOUNTADMIN role.|
|DATA_TRANSFER_HISTORY||Last 14 days||Results returned only for the ACCOUNTADMIN role. |
|DATABASE_STORAGE_USAGE_HISTORY||Last 6 months||Results returned only for the ACCOUNTADMIN role. |
|LOGIN_HISTORY , LOGIN_HISTORY_BY_USER||Last 7 days||Results returned depend on the privileges assigned to the user’s current role.|
|PIPE_USAGE_HISTORY||Last 14 days||Results returned only for the ACCOUNTADMIN role. |
|QUERY_HISTORY , QUERY_HISTORY_BY_*||Last 7 days||Results returned depend on the privileges assigned to the user’s current role.|
|STAGE_STORAGE_USAGE_HISTORY||Last 6 months||Results returned only for the ACCOUNTADMIN role. |
|WAREHOUSE_LOAD_HISTORY||Last 14 days||Results returned only for the ACCOUNTADMIN role. |
|WAREHOUSE_METERING_HISTORY||Last 6 months||Results returned only for the ACCOUNTADMIN role. |
 This function also supports the MONITOR USAGE privilege, which can be used to return results for roles other than ACCOUNTADMIN.
Entity Relationship Diagram (ERD) for Views¶
The ERD for the views in the INFORMATION_SCHEMA schema is too large to display in this topic. To view and/or download the ERD in PDF format, click the following image:
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 Information Schema is 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 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.
Querying Information Schema Views and Table Functions¶
When querying an INFORMATION_SCHEMA view or table function, the INFORMATION_SCHEMA schema must be in use for the session or you must used the fully-qualified name of the view/table function.
SELECT * FROM testdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ; SELECT * FROM TABLE(testdb.INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
orUSE DATABASE testdb; SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ; SELECT * FROM TABLE(INFORMATION_SCHEMA.LOGIN_HISTORY( ... ));
orUSE SCHEMA testdb.INFORMATION_SCHEMA; SELECT * FROM TABLES WHERE TABLE_SCHEMA = 'PUBLIC' ... ; SELECT * FROM TABLE(LOGIN_HISTORY( ... ));
For more detailed examples, see the reference documentation for each view/table function.