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.

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.

List of Information Schema 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.).

Information Schema views that are Snowflake-specific (that is, that are not ANSI-standard) are marked with ✔ in the “Snowflake-specific” column in the following table.

List of Information Schema 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:

_{[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 *.

Considerations for replacing SHOW commands with Information Schema views¶

The INFORMATION_SCHEMA views provide 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:

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:

  Copy codeExpand code block

  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( ... ));

  Show lessSee more

  Scroll to top

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

  Copy codeExpand code block

  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( ... ));

  Show lessSee more

  Scroll to top

• To query with the INFORMATION_SCHEMA schema in use for the session:

  Copy codeExpand code block

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

  Show lessSee more

  Scroll to top

  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.