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

user_12098165 0
Viewed 76

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.

snowflake-schema snowflake-cloud-data-platform
3 Answers

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:

enter image description here

user_50708790

Check this. You can download the results and use it to get the DDL of all views, at once.

SELECT 'SELECT GET_DDL(''VIEW'',''' || table_name || ''');' AS stmt
FROM INFORMATION_SCHEMA.views 
WHERE table_name NOT IN('TABLES','COLUMNS','SCHEMATA','SEQUENCES','VIEWS','TABLE_PRIVILEGES','USAGE_PRIVILEGES','DATABASES','REPLICATION_DATABASES','REPLICATION_GROUPS','FUNCTIONS','PROCEDURES','OBJECT_PRIVILEGES','OBJECT_PRIVILEGES','FILE_FORMATS','APPLICABLE_ROLES','ENABLED_ROLES','STAGES','REFERENTIAL_CONSTRAINTS','TABLE_CONSTRAINTS','INFORMATION_SCHEMA_CATALOG_NAME','LOAD_HISTORY','TABLE_STORAGE_METRICS','PIPES','EXTERNAL_TABLES','LOGGERS','EVENT_TABLES','PACKAGES');
user_123191200

Thanks all for suggesting your solutions. I found below code is much more close to my requirement; copy entire VIEWS in one go:

select view_definition from information_schema.views
where table_schema = 'XYZ'

Copy the the view_definition > Execute it.

user_120981650
Related