CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Simon
Simon

Reputation: 1111

Drop schemas based on pattern match in Snowflake

What is the best way to drop all schemas with a user-specified prefix from a database in Snowflake (ANSI SQL)?

Suppose you have a database called ANALYTICS and within that database there are multiple schemas such as: dev_bob_schema1, dev_bob_schema2, dev_alice_schema1.

How can you drop only those schemas which are prepended dev_bob?

I've got to the stage where I can list the schemas with:

use database "ANALYTICS";
select schema_name
from information_schema.schemata
where schema_name ilike 'dev_bob%'

But I can't overcome the final hurdle to loop through the response and execute drop schema ...

Upvotes: 3

Views: 2509

Answers (2)

NickW
NickW

Reputation: 9808

I would do the following:

  1. USE DATABASE ...
  2. show schemas like ...
  3. SELECT 'DROP SCHEMA ' || "name" || ';' FROM table(result_scan(last_query_id()));

Paste the output back into a worksheet and run it.

Upvotes: 6

Lukasz Szozda
Lukasz Szozda

Reputation: 176114

Using INFORMATION_SCHEMA.SCHEMATA and Snowflake Scripting:

Setup:

CREATE DATABASE ANALYTICS;
CREATE SCHEMA dev_bob_schema1;
CREATE SCHEMA dev_bob_schema2;
CREATE SCHEMA dev_bob_schema3;

Query:

SELECT LISTAGG(CONCAT('DROP SCHEMA IF EXISTS ', SCHEMA_NAME, ';', CHAR(10)) , '') AS output
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME ILIKE 'DEV_BOB_SCHEMA%';

Output:

DROP SCHEMA IF EXISTS DEV_BOB_SCHEMA1; 
DROP SCHEMA IF EXISTS DEV_BOB_SCHEMA2; 
DROP SCHEMA IF EXISTS DEV_BOB_SCHEMA3;

All-in-one:

-- dropping schemas and generating output
DECLARE
   OUTPUT STRING DEFAULT '';
   CUR    CURSOR FOR SELECT CONCAT('DROP SCHEMA IF EXISTS ', SCHEMA_NAME) AS query
                     FROM INFORMATION_SCHEMA.SCHEMATA
                     WHERE SCHEMA_NAME ILIKE 'DEV_BOB_SCHEMA%';
BEGIN

   FOR rec IN CUR DO
       OUTPUT := OUTPUT || rec.query || CHAR(10);
       EXECUTE IMMEDIATE rec.query;
   END FOR;
   
   RETURN :OUTPUT;
END;

Output(Classic UI):

enter image description here

Upvotes: 0

Related Questions