How to get definition of all the VIEWS in a Snowflake database?

Question

The ask is to get the definition of all the views from Production environment and refresh the lower environment. I assume, GET_DDL in a loop will suffice the need but not sure how to implement it. Please advise.

Answer 1

Assuming that VIEW_DEFITION is not enough:

SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA != 'INFORMATION_SCHEMA';

and GET_DDL is required:

DECLARE
CUR CURSOR FOR SELECT CONCAT_WS('.',TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME) AS name
               FROM INFORMATION_SCHEMA.VIEWS
               WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA');
BEGIN
  CREATE OR REPLACE TEMPORARY TABLE temp_view_defs(view_name TEXT, definition TEXT);

  FOR rec IN CUR DO   
    EXECUTE IMMEDIATE REPLACE('INSERT INTO temp_view_defs(view_name, definition)
                        SELECT ''<view_name>'', GET_DDL(''TABLE'', ''<view_name>'')'
                        ,'<view_name>'
                        ,rec.name);
 END FOR;

 LET rs RESULTSET := (SELECT * FROM temp_view_defs);

 RETURN TABLE(rs);
END;

Sample output: