snowflake drop all pipes like pattern

Question

I would like to drop all pipes in a snowflake schema that match a pattern.

You can show pipes that match a pattern as shown here.

Example: show pipes like '%NAME_LIKE_THIS%' in MY_DB.MY_SCHEMA

However, it doesn't appear that a similar functionality exists for drop pipe.

I'm thinking of creating a stored procedure that will take in pattern and schema parameters, and iterate through each and drop but I'm hoping there's a better/easier way.

Thank you in advance.

Answer 1

You can use a SQL generator to do this.

show pipes like '%NAME_LIKE_THIS%' in MY_DB.MY_SCHEMA;

select 'drop pipe MY_DB.MY_SCHEMA.' || "name" || ' in MY_DB.MY_SCHEMA;' as SQL_COMMAND from table(result_scan(last_query_id()));

If you want to automate dropping the pipes, you can write a stored procedure that loops through all dropping one at a time.

If you don't want to write a custom stored procedure, here's a stored procedure I wrote to execute the commands from a SQL generator one at a time:

https://support.snowflake.net/s/article/Executing-Multiple-SQL-Statements-in-a-Stored-Procedure