In Snowflake, while individual views lack an UNDROP command, restoring a dropped database using UNDROP DATABASE reinstates all its contained objects, including views, provided the restoration occurs within the data retention period. We know This is because the UNDROP operation for databases and schemas leverages Snowflake's Time Travel feature, which preserves the state of these objects for a specified duration after deletion.
But why it can undrop the view in db but not individual one?
As stated in the documentation, Dropped views cannot be recovered; they must be recreated. view once deleted cannot be undropped because View is more like storing query statements than storing data.
Also, we do not have an undrop for a view.
https://docs.snowflake.com/en/sql-reference/sql/undrop#syntax
However, you could use the query_history view to get the SQL of the dropped view.
https://docs.snowflake.com/en/sql-reference/account-usage
https://docs.snowflake.com/en/sql-reference/info-schema#list-of-table-functions
You can use the QUERY_ID or QUERY_TEXT as a filter to retrieve the view definition.
select *
from table(information_schema.query_history())
where QUERY_TEXT ilike 'create view <name>%'
order by start_time;
select *
from table(information_schema.query_history())
where QUERY_ID = '<query_ID>'
order by start_time;