Information Schema

The Snowflake Information Schema consists of a set of system-defined views and functions that provide metadata information about the objects in your account:

  • The views describe database objects, as well as non-database objects, defined for your account, including:
    • Views from the Information Schema SQL standard that are relevant to Snowflake (tables, columns, views, etc).
    • Views for the non-standard database objects that Snowflake supports (e.g. stages and file formats).
    • Views for other, non-database objects (e.g. roles).
  • The functions provide historical information, including:
    • User login history.
    • Query history.
    • Data storage usage for databases and stages.
    • Warehouse usage.

In this Topic:

What is INFORMATION_SCHEMA?

Each database in Snowflake includes a built-in, read-only schema named INFORMATION_SCHEMA. This is the schema that contains all the Information Schema views and functions for a database, as well as the additional views and functions for non-database objects across your account.

When querying views or functions in INFORMATION_SCHEMA, the fully-qualified name must be specified (unless the database and/or schema are in use for the session), in the form of:

<db_name>.INFORMATION_SCHEMA.<view_name>

<db_name>.INFORMATION_SCHEMA.<function_name>( ... )

For more information, see Query Examples below.

Information Schema Views

The views in INFORMATION_SCHEMA display metadata about all objects defined in the database, as well as Snowflake objects that are common across all databases, e.g. roles. The contents of a view depend on the privileges granted to the user’s current role. When querying an INFORMATION_SCHEMA view, only objects for which the current role has been granted access privileges are returned.

Entity Relationship Diagram (ERD)

The ERD for the views in the INFORMATION_SCHEMA schema is too large to display here. To view and/or download the ERD in PDF format, click the following link:

Information Schema Functions

In addition to views, each INFORMATION_SCHEMA schema also includes a set of built-in table functions that can be used to return historical information about executed queries, data stored in databases and stages, and warehouse usage.

General Usage Notes

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

  • Queries on Information Schema views do not guarantee consistency with respect to concurrent DDLs. 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 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.

  • To control system usage and prevent performance issues, if an Information Schema query lacks sufficiently selective filters, the following error is returned:

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

Differences Between Information Schema Views and SHOW commands

The Information Schema provides a SQL interface to the same information provided by the SHOW commands. You can use the views in the Information Schema to replace these commands; however, there are some differences to consider when converting SHOW commands into Information Schema queries:

  • Warehouse requirements:

    SHOW:None.
    Info Schema:A running, current warehouse for the session is required to query the views.
  • Pattern matching/filtering:

    SHOW:Case-insensitive comparison (for the commands that support LIKE filtering).
    Info Schema:Standard (case-sensitive) SQL comparison semantics. Snowflake automatically converts unquoted, case-insensitive identifiers to uppercase internally, so an unquoted object name appears in upper case in the Information Schema views.
  • Query results:

    SHOW:Most commands restrict results to the current schema by default.
    Info Schema:Views display all objects in the current database. To query only against the current schema, use a filter such as ... WHERE table_schema = CURRENT_SCHEMA(). Information Schema queries that lack sufficiently selective filters return an error and do not execute (see General Usage Notes above).

Query Examples

With no current database for the session, query the TABLES View for a database named testdb:

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

--------------------+------------------+
 CURRENT_DATABASE() | CURRENT_SCHEMA() |
--------------------+------------------+
 [NULL]             | [NULL]           |
--------------------+------------------+

SELECT table_name, table_type
FROM testdb.information_schema.tables
WHERE table_schema = 'PUBLIC';

Same query as above, but with testdb as the current database for the session:

USE DATABASE testdb;

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

--------------------+------------------+
 CURRENT_DATABASE() | CURRENT_SCHEMA() |
--------------------+------------------+
 TESTDB             | PUBLIC           |
--------------------+------------------+

SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = current_schema();

Same query as above, but with INFORMATION_SCHEMA for testdb as the current schema for the session:

USE SCHEMA testdb.information_schema;

SELECT CURRENT_DATABASE(), CURRENT_SCHEMA();

--------------------+--------------------+
 CURRENT_DATABASE() |  CURRENT_SCHEMA()  |
--------------------+--------------------+
 TESTDB             | INFORMATION_SCHEMA |
--------------------+--------------------+

SELECT table_name, table_type
FROM tables
WHERE table_schema = 'PUBLIC';