in snowflake, do you know how to drop internal stage quickly. I have 13k internal stages…
- User
in snowflake, do you know how to drop internal stage quickly. I have 13k internal stages created in the schema, and there are many tables too. I'd like to quickly drop all the stages but not impact other objects like table or functions
Assistant
You're in a common situation where quick cleanup is needed without affecting other critical objects. In Snowflake, the best way to do this is by leveraging the
INFORMATION_SCHEMAviews along with SQL Scripting (for a stored procedure) or generating DDL statements.Here are two methods, with the Stored Procedure being the most robust and recommended for 13,000 stages.
Method 1: Generate
DROP STAGEStatements (Manual Execution)This method generates a list of
DROP STAGEcommands that you can then copy and paste into a new worksheet and execute.Pros: Simple, no stored procedure creation needed. Cons: If you have 13,000 stages, the generated script will be very long. Pasting and executing such a large script might cause performance issues in your client (Snowsight, DBeaver, etc.) or hit query size limits.
-
Switch to the correct database and schema:
USE DATABASE YOUR_DATABASE_NAME; USE SCHEMA YOUR_SCHEMA_NAME;(Replace
YOUR_DATABASE_NAMEandYOUR_SCHEMA_NAME) -
Generate the
DROP STAGEcommands:SELECT 'DROP STAGE IF EXISTS "' || STAGE_CATALOG || '"."' || STAGE_SCHEMA || '"."' || STAGE_NAME || '";' AS drop_command FROM INFORMATION_SCHEMA.STAGES WHERE STAGE_CATALOG = CURRENT_DATABASE() -- Or 'YOUR_DATABASE_NAME' AND STAGE_SCHEMA = CURRENT_SCHEMA(); -- Or 'YOUR_SCHEMA_NAME' ORDER BY STAGE_NAME; -
Review and Execute:
- Copy all the output from the
drop_commandcolumn. - Paste it into a new Snowflake worksheet.
- Execute the script.
- Copy all the output from the
Method 2: Stored Procedure (Recommended for Large Scale)
This is the most efficient and robust way to drop a large number of stages automatically. It uses Snowflake's SQL Scripting feature within a stored procedure to iterate and execute the
DROP STAGEcommands.Pros: Automated, handles large numbers of objects gracefully, can include a dry run, error handling. Cons: Requires creating a stored procedure.
Steps:
-
Create the Stored Procedure:
CREATE OR REPLACE PROCEDURE DROP_ALL_STAGES_IN_SCHEMA( database_name VARCHAR, schema_name VARCHAR, dry_run BOOLEAN ) RETURNS VARCHAR LANGUAGE SQL AS $$ DECLARE drop_command VARCHAR; stage_cursor CURSOR FOR SELECT STAGE_NAME FROM INFORMATION_SCHEMA.STAGES WHERE STAGE_CATALOG = :database_name AND STAGE_SCHEMA = :schema_name; dropped_count INT DEFAULT 0; total_count INT DEFAULT 0; stage_name_var VARCHAR; results_message VARCHAR; BEGIN -- Count total stages first SELECT COUNT(*) INTO total_count FROM INFORMATION_SCHEMA.STAGES WHERE STAGE_CATALOG = :database_name AND STAGE_SCHEMA = :schema_name; IF (total_count = 0) THEN RETURN 'No stages found in schema "' || database_name || '"."' || schema_name || '".'; END IF; IF (dry_run) THEN results_message := 'DRY RUN: Commands that WOULD be executed for ' || total_count || ' stages in "' || database_name || '"."' || schema_name || '":\n'; FOR record IN stage_cursor DO stage_name_var := record.STAGE_NAME; drop_command := 'DROP STAGE IF EXISTS "' || database_name || '"."' || schema_name || '"."' || stage_name_var || '";'; results_message := results_message || drop_command || '\n'; END FOR; RETURN results_message; ELSE results_message := 'Executing actual DROP commands for ' || total_count || ' stages in "' || database_name || '"."' || schema_name || '":\n'; FOR record IN stage_cursor DO stage_name_var := record.STAGE_NAME; drop_command := 'DROP STAGE IF EXISTS "' || database_name || '"."' || schema_name || '"."' || stage_name_var || '";'; results_message := results_message || 'Attempting to drop: ' || drop_command || '\n'; -- Execute the drop command EXECUTE IMMEDIATE drop_command; dropped_count := dropped_count + 1; END FOR; results_message := results_message || '\nSuccessfully dropped ' || dropped_count || ' out of ' || total_count || ' stages.'; RETURN results_message; END IF; EXCEPTION WHEN OTHER THEN RETURN 'Error dropping stages: ' || SQLERRM || '\n' || results_message; END; $$; -
Test with a Dry Run (Highly Recommended!): Before actually dropping anything, run the procedure in
dry_runmode to see exactly which stages it would attempt to drop.CALL DROP_ALL_STAGES_IN_SCHEMA('YOUR_DATABASE_NAME', 'YOUR_SCHEMA_NAME', TRUE);Replace
YOUR_DATABASE_NAMEandYOUR_SCHEMA_NAMEwith your actual database and schema names. Review the output carefully. It will list all theDROP STAGEcommands it would execute. -
Execute the Procedure (Actual Drop): Once you are confident with the
dry_runoutput, execute the procedure withdry_runset toFALSE.CALL DROP_ALL_STAGES_IN_SCHEMA('YOUR_DATABASE_NAME', 'YOUR_SCHEMA_NAME', FALSE);This will actually drop the stages. Be absolutely sure before running this!
Important Considerations:
- Permissions: The role executing the stored procedure (or the user running Method 1) must have
OWNERSHIPon the stages or sufficient privileges (OPERATEon the stages andUSAGEon the database/schema). A role withOWNERSHIPon the schema typically has ownership on objects created within it. - Case Sensitivity: Snowflake object names are case-insensitive by default unless they are double-quoted during creation (e.g.,
CREATE STAGE "MyStage"). The provided SQL uses double quotes aroundSTAGE_CATALOG,STAGE_SCHEMA, andSTAGE_NAMEto ensure it works correctly regardless of whether the names are case-sensitive or not. - External Stages: The
INFORMATION_SCHEMA.STAGESview includes both internal and external stages. If you only want to drop internal stages and not external ones, you would need to add an additional filter to theWHEREclause:AND STAGE_TYPE = 'INTERNAL'(thoughCREATE STAGEwithout aURLautomatically creates internal stages, so typically all stages in your schema will be internal unless you explicitly created external ones there). - Dependent Objects: Stages themselves do not have direct dependencies in the same way tables or functions do. Dropping a stage will not directly impact a table that references it (e.g., in a
COPY INTOcommand), but any futureCOPY INTOorPUToperations using that dropped stage will, of course, fail. - Speed: For 13,000 stages, the stored procedure will be significantly faster and more reliable than trying to execute a single massive script. Snowflake's internal processing for SQL scripting is optimized for such batch operations.
Choose the method that best suits your comfort level and the environment you're working in. The stored procedure is generally the safer and more scalable option for large-scale operations.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.