Information Schema

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

In this Topic:


In the ANSI Information Schema, the term “catalog” refers to a database. To maintain compatibility with the standard, we use the term “catalog” in the Snowflake Information Schema to represent a database, except for the Snowflake-specific DATABASES view. The terms are conceptually equivalent.

What is the INFORMATION_SCHEMA Schema?

Each database created in your account includes a built-in, read-only schema named INFORMATION_SCHEMA. This is the schema that contains all the Information Schema views and functions for the 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.<function_name>( ... )

For more information, see Examples of Querying Using Fully-Qualified Names (in this topic).

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:

  • Standard Information Schema views for the database and non-database objects that are relevant to Snowflake (tables, columns, views, roles, etc.).
  • Views for the non-standard objects that Snowflake supports (stages, file formats, etc.).

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.

List of Views

View Type Notes
COLUMNS Database  
DATABASES Account Snowflake-specific
FILE FORMATS Database Snowflake-specific
FUNCTIONS Database  
LOAD_HISTORY Account Snowflake-specific
SCHEMATA Database  
SEQUENCES Database  
STAGES Database Snowflake-specific
TABLE_STORAGE_METRICS Database Snowflake-specific
TABLES Database  
VIEWS Database  

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

Entity Relationship Diagram (ERD) for Views

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

Considerations for Using Information Schema Views vs 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:

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 (for the SHOW commands that support LIKE filtering). Standard (case-sensitive) SQL 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 Most SHOW commands restrict results to the current schema by default. 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(). Note that Information Schema queries lacking sufficiently selective filters return an error and do not execute (see General Usage Notes in this topic).

Information Schema Functions

In addition to views, each INFORMATION_SCHEMA schema includes a set of built-in table functions that can be used to return the following usage and historical information for storage usage warehouse usage, user logins, and queries:

Table Function Data Retention Notes
DATABASE_STORAGE_USAGE_HISTORY 6 months Results returned only for the ACCOUNTADMIN role. [1]
LOGIN_HISTORY , LOGIN_HISTORY_BY_USER 7 days Results returned depend on the privileges assigned to the user’s current role.
QUERY_HISTORY , QUERY_HISTORY_BY_* 7 days Results returned depend on the privileges assigned to the user’s current role.
STAGE_STORAGE_USAGE_HISTORY 6 months Results returned only for the ACCOUNTADMIN role. [1]
WAREHOUSE_LOAD_HISTORY 14 days Results returned only for the ACCOUNTADMIN role. [1]
WAREHOUSE_METERING_HISTORY 6 months Results returned only for the ACCOUNTADMIN role. [1]

[1] Results also returned if the current role has been granted the MONITORING USAGE privilege.

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.

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

Examples of Querying Using Fully-Qualified Names

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


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



 TESTDB             | PUBLIC           |

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

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

USE SCHEMA testdb.information_schema;



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

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