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.
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.
- non-database objects such as roles, warehouses, and databases)
- Table functions for historical and usage data across your account.
General Usage Notes:
- Each INFORMATION_SCHEMA schema is read-only i.e. cannot be modified or dropped.
- Queries on INFORMATION_SCHEMA views do not guarantee consistency with respect to concurrent DDL.
- For example, if tables created while a long-running INFORMATION_SCHEMA query executes. 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. 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:
Retrieve up to the last 100 queries run in the current session:
select * from table(information_schema.query_history_by_session()) order by start_time; select * from table(information_schema.query_history_by_warehouse()) order by start_time; select * from table(information_schema.query_history_by_user()) order by start_time;
Get up to the last 100 queries run by the current user (or run by any user on any warehouse on which the current user has the MONITOR privilege:
select * from table(information_schema.query_history()) order by start_time;
Fetch up to the last 100 queries run in the past hour by the current user.
select * from table(information_schema.query_history(dateadd('hours',-1,current_timestamp()),current_timestamp())) order by start_time;
Extract all queries run by the current user (or run by any user on any warehouse on which the current user has the MONITOR privilege) within a specified 30 minute block of time within the past 15 days:
select * from table(information_schema.query_history( end_time_range_start=>to_timestamp_ltz('2020-09-11 12:00:00.000 -0700'), end_time_range_end=>to_timestamp_ltz('2020-09-24 12:30:00.000 -0700')));