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 ...

CodePudding user response：

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.