I have specific requirement where I need to find only DDL ran against my snowflake database/schema during specific period of time , how can we find using query_history or any other method, any idea??
1 Answers
It is possible using QUERY_HISTORY:
SELECT *
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY())
-- END_TIME_RANGE_START/END_TIME_RANGE_END to get specific time range
WHERE DATABASE_NAME ILIKE '<db_name>'
AND SCHEMA_NAME ILIKE '<schema_name>'
AND QUERY_TYPE ILIKE ANY ('CREATE%', 'ALTER%', 'DROP%', 'DESCRIBE%');
The QUERY_TYPE column contains values that are more descriptive like: CREATE_VIEW/CREATE_TABLE_AS_SELECT/CRATE_TABLE/ALTER_TABLE_ADD_COLUMN etc.
To retrieve entire class of DDL commands, wildcard pattern was used CREATE% or ALTER%. It could be further tweaked depending on specific needs.
Related