Snowflake Information Schema¶

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¶

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 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:

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.