Skip to content
TheChatArchiveThe Chat Archive
Geminigemini-2.5-flash· GoogleHugging Face LMArena Human Preference 140K

in snowflake, do you know how to drop internal stage quickly. I have 13k internal stages…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with DeepSeekdeepseek-v3-0324’s answer.
  1. 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

  2. Gemini 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_SCHEMA views 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 STAGE Statements (Manual Execution)

    This method generates a list of DROP STAGE commands 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.

    1. Switch to the correct database and schema:

      USE DATABASE YOUR_DATABASE_NAME;
      USE SCHEMA YOUR_SCHEMA_NAME;
      

      (Replace YOUR_DATABASE_NAME and YOUR_SCHEMA_NAME)

    2. Generate the DROP STAGE commands:

      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;
      
    3. Review and Execute:

      • Copy all the output from the drop_command column.
      • Paste it into a new Snowflake worksheet.
      • Execute the script.

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

    Pros: Automated, handles large numbers of objects gracefully, can include a dry run, error handling. Cons: Requires creating a stored procedure.

    Steps:

    1. 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;
      $$;
      
    2. Test with a Dry Run (Highly Recommended!): Before actually dropping anything, run the procedure in dry_run mode 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_NAME and YOUR_SCHEMA_NAME with your actual database and schema names. Review the output carefully. It will list all the DROP STAGE commands it would execute.

    3. Execute the Procedure (Actual Drop): Once you are confident with the dry_run output, execute the procedure with dry_run set to FALSE.

      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 OWNERSHIP on the stages or sufficient privileges (OPERATE on the stages and USAGE on the database/schema). A role with OWNERSHIP on 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 around STAGE_CATALOG, STAGE_SCHEMA, and STAGE_NAME to ensure it works correctly regardless of whether the names are case-sensitive or not.
    • External Stages: The INFORMATION_SCHEMA.STAGES view 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 the WHERE clause: AND STAGE_TYPE = 'INTERNAL' (though CREATE STAGE without a URL automatically 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 INTO command), but any future COPY INTO or PUT operations 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.