Generating DROP Statements in Snowflake (Part II)

Introduction

This is a follow up on my blogpost about dropping database objects (view, table and stored procedure) in a database. This was just a script but I want to have a stored procedure with parameters : one for the database and one for the included schemas. I only want to create drop statements for certain schemas.

Creating the demo 

For this blogpost I've created an example with two schemas in a database. In these schemas I've created a couple of tables, views and stored procedure.

CREATE OR REPLACE DATABASE BUILDDROPSv4;
CREATE OR REPLACE SCHEMA SCHEMAA;
CREATE OR REPLACE SCHEMA SCHEMAB;

USE SCHEMA SCHEMAA;

CREATE TABLE A(A STRING);
CREATE TABLE B(A STRING);
CREATE TABLE C(C STRING);
CREATE VIEW VW_A AS SELECT * FROM A;
CREATE VIEW VW_B AS SELECT * FROM B;
CREATE VIEW VW_C AS SELECT * FROM C;

USE SCHEMA SCHEMAB;
CREATE TABLE D(D STRING);
CREATE TABLE E(E STRING);

CREATE PROCEDURE LOAD_A(PARTIME TIMESTAMP_NTZ) 
RETURNS STRING
LANGUAGE SQL 
AS 
BEGIN 
    SELECT 1; 
    RETURN ('SUCCEEDED');
END;

CREATE PROCEDURE LOAD_B(PARTIME TIMESTAMP_NTZ, ANOTHERPARAMETER STRING) 
RETURNS STRING
LANGUAGE SQL 
AS 
BEGIN 
    SELECT 1; 
    RETURN ('SUCCEEDED');
END;

The script in a stored procedure

I've copied the DROP script from my previous blogpost into a stored procedure and made it a bit dynamic. Now you can execute this stored procedure with two parameters

• Database name 
• Included schemas

CREATE OR REPLACE PROCEDURE DROP_OBJECTS (DATABASE_NAME STRING, INCLUDED_SCHEMAS STRING) 
RETURNS STRING
LANGUAGE SQL 
AS         
DECLARE

 SQL_STATEMENT STRING DEFAULT '
       WITH CTE_TABLES AS (
        SELECT 
                TABLE_CATALOG ||''.''|| TABLE_SCHEMA || ''.''|| TABLE_NAME AS TABLE_NAME,
                T.TABLE_TYPE AS OBJECT_TYPE, 
                1 AS SORT_ORDER
            FROM ' || :DATABASE_NAME  || '.INFORMATION_SCHEMA.TABLES T
            WHERE T.TABLE_SCHEMA NOT IN (''INFORMATION_SCHEMA'')
            AND T.TABLE_SCHEMA IN (' || :INCLUDED_SCHEMAS || ') 
            AND TABLE_TYPE = ''BASE TABLE''
        ),
        CTE_VIEWS AS (
            SELECT 
                TABLE_CATALOG ||''.''|| TABLE_SCHEMA ||''.''|| TABLE_NAME AS VIEW_NAME,
                T.TABLE_TYPE AS OBJECT_TYPE, 
                2 AS SORT_ORDER
            FROM ' || :DATABASE_NAME  || '.INFORMATION_SCHEMA.TABLES T
            WHERE T.TABLE_SCHEMA NOT IN (''INFORMATION_SCHEMA'')
            AND T.TABLE_SCHEMA IN (' || :INCLUDED_SCHEMAS || ')
            AND TABLE_TYPE = ''VIEW''
        
        ),
        CTE_PROC_BASE AS (
            SELECT 
                PROCEDURE_CATALOG , 
                PROCEDURE_SCHEMA, 
                PROCEDURE_NAME, 
                ARGUMENT_SIGNATURE, 
                SPLIT(TRIM(ARGUMENT_SIGNATURE, ''()''), '','') AS ARG_ARRAY 
            FROM ' || :DATABASE_NAME  || '.INFORMATION_SCHEMA.PROCEDURES
            WHERE PROCEDURE_SCHEMA IN (' || :INCLUDED_SCHEMAS || ')
        ),
        CTE_PROC_ARRAY_VALUES AS ( 
            SELECT 
                MAX(PROCEDURE_CATALOG) AS PROCEDURE_CATALOG, 
                MAX(PROCEDURE_SCHEMA) AS PROCEDURE_SCHEMA, 
                PROCEDURE_NAME, 
                ARRAY_AGG(SPLIT_PART(LTRIM(d.value,'' ''),'' '', 2)) AS FILTERED_VALUES,
                3
            FROM  CTE_PROC_BASE, LATERAL FLATTEN(ARG_ARRAY) d
            GROUP BY ARG_ARRAY, PROCEDURE_NAME
        ),
        CTE_PROCEDURES AS (
            SELECT 
                PROCEDURE_CATALOG ||''.''|| PROCEDURE_SCHEMA ||''.''|| PROCEDURE_NAME|| ''('' || REPLACE (TRIM (FILTERED_VALUES::varchar,''[]''),''"'', '' '' ) || '')'' AS PROCEDURE_NAME,
                ''PROCEDURE'' AS OBJECT_TYPE,
                3 AS SORT_ORDER
            FROM CTE_PROC_ARRAY_VALUES
        )
        SELECT OBJECT_NAME, OBJECT_TYPE
        FROM 
            (SELECT TABLE_NAME AS OBJECT_NAME, OBJECT_TYPE, SORT_ORDER FROM CTE_TABLES
            UNION ALL
            SELECT VIEW_NAME,  OBJECT_TYPE, SORT_ORDER FROM CTE_VIEWS
            UNION ALL 
            SELECT PROCEDURE_NAME, OBJECT_TYPE, SORT_ORDER FROM CTE_PROCEDURES
            )
        ORDER BY SORT_ORDER;'; 
  
  RES RESULTSET DEFAULT (EXECUTE IMMEDIATE :SQL_STATEMENT);
  TAB_CURSOR CURSOR FOR RES;
    
  var_OBJECT_NAME VARCHAR;
  var_OBJECT_TYPE VARCHAR;
  
  QUERY STRING DEFAULT '';

BEGIN
  OPEN TAB_CURSOR;
  LOOP
    FETCH TAB_CURSOR into var_OBJECT_NAME, var_OBJECT_TYPE ;
    IF (var_OBJECT_TYPE = 'BASE TABLE') THEN
       QUERY := QUERY  || 'DROP TABLE IF EXISTS ' || var_OBJECT_NAME || '; \r';  
    ELSEIF (var_OBJECT_TYPE = 'VIEW') THEN
       QUERY := QUERY  || 'DROP VIEW IF EXISTS ' || var_OBJECT_NAME|| '; \r';  
    ELSEIF(var_OBJECT_TYPE = 'PROCEDURE') THEN   
       QUERY := QUERY  || 'DROP PROCEDURE IF EXISTS ' || var_OBJECT_NAME || '; \r';  
    ELSE
      BREAK;   
    END IF;
  END LOOP;
  CLOSE TAB_CURSOR;
  RETURN (:QUERY);
END;

Now we call the stored procedure with the following parameters:

CALL SCHEMAB.DROP_OBJECTS('BUILDDROPSV4', '''SCHEMAA''');

And this will result in the following script :

DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.C; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.B; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.A; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_C; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_B; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_A; 

Call the stored procedure but then with another parameter (SCHEMAB)

CALL SCHEMAB.DROP_OBJECTS('BUILDDROPSV4', '''SCHEMAB''');

And this will result in the following script :

DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAB.D; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAB.E; 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.LOAD_B( TIMESTAMP_NTZ , VARCHAR ); 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.LOAD_A( TIMESTAMP_NTZ ); 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.DROP_OBJECTS( VARCHAR , VARCHAR );

And the final test with two schemas :

CALL SCHEMAB.DROP_OBJECTS('BUILDDROPSV4', '''SCHEMAA'', ''SCHEMAB''');

And this will result in the following script :

DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.C; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.B; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAB.E; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAB.D; 
DROP TABLE IF EXISTS BUILDDROPSV4.SCHEMAA.A; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_B; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_C; 
DROP VIEW IF EXISTS BUILDDROPSV4.SCHEMAA.VW_A; 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.DROP_OBJECTS( VARCHAR , VARCHAR ); 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.LOAD_A( TIMESTAMP_NTZ ); 
DROP PROCEDURE IF EXISTS BUILDDROPSV4.SCHEMAB.LOAD_B( TIMESTAMP_NTZ , VARCHAR );  

Final thoughts

I've improved the script by using it in a stored procedure and with a couple of parameters to make it more dynamic.

Hennie