CODEWITHSUNDEEP
snowflake-cloud-data-platform
csharpvsto
csharpvsto

Reputation: 89

Retrieve Overwritten View SnowFlake

Problem: I have accidentally overwrite a view in SnowFlake using CREATE OR REPLACE VIEW. Question: Is there anyway to retrieve the old view i.e. the SQL code?

Upvotes: 1

Views: 3561

Answers (2)

Greg Pavlik
Greg Pavlik

Reputation: 11046

If you can't find it in the query history tab using QUERY_TYPE > CREATE, you can search for it over the previous 365 days in the query history. This previous post has the SQL to run:

View DDL history of CREATE VIEW statement in Snowflake

Note that this is a big query if your account has run lots of queries over the last year. You can modify it to reduce the scan if necessary if you know more information, such as the month of creation.

If you want a totally informal and lightweight way to version your objects, I wrote one for my internal use that I decided to share. It's a table and stored procedure. Call the stored procedure with the object type and three-part name, and it adds a version row to the table. If it finds a pervious version, it marks the old one obsolete as of the current_timestamp and increments the number of the new version by 1.

https://github.com/GregPavlik/SimpleVersioning/blob/main/install.sql

Upvotes: 2

CMe
CMe

Reputation: 692

You can use QUERY_HISTORY to find the previous DDL used to create the query.

You can filter results using QUERY_TYPE which will help to you to find quickly the right query type.

Upvotes: 2

Related Questions