How to Drop All Tables in Snowflake

How do I drop all tables in a Snowflake schema?

Removes every table from a chosen Snowflake database-schema in a single operation.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does “drop all tables” mean in Snowflake?

Dropping all tables deletes every table object in a target database-schema, freeing storage and releasing names. The action is irreversible; back up critical data first.

How do I drop all tables in a Snowflake schema quickly?

Generate DROP TABLE statements with INFORMATION_SCHEMA, aggregate them with LISTAGG, and run the resulting script with EXECUTE IMMEDIATE. This avoids manual deletion and respects transactional order.

Quick one-liner

EXECUTE IMMEDIATE ( SELECT LISTAGG('DROP TABLE "' || table_schema || '"."' || table_name || '";', ' ') FROM MYDB.INFORMATION_SCHEMA.TABLES WHERE table_schema = 'PUBLIC');

Which roles and privileges are required?

The executing role needs OWNERSHIP or DROP privilege on each table plus USAGE on the database and schema. SYSADMIN usually suffices.

Can I limit the drop to specific patterns?

Yes. Add AND table_name ILIKE '%TEST%' in the WHERE clause to target only matching tables, keeping production tables safe.

How to preview tables before dropping?

Run the SELECT portion alone to list candidate tables. Verify the list, then run EXECUTE IMMEDIATE when satisfied.

Best practices for mass drops

• Always run in a sandbox or off-hours.
• Take a clone of the schema for rollback.
• Use separate dev and prod warehouses to avoid accidental data loss.

Common mistakes and fixes

Missing schema filter: Omitting WHERE table_schema='PUBLIC' deletes tables in every schema. Always filter.
Insufficient privileges: Running as a role without OWNERSHIP causes partial drops. Switch to SYSADMIN or GRANT needed rights.

Why prefer dynamic SQL over manual drops?

Dynamic SQL scales to hundreds of tables, guarantees consistent syntax, and reduces human error compared with copy-pasting individual DROP statements.

Is there a built-in single command?

Snowflake lacks a native DROP SCHEMA CASCADE equivalent, so dynamic SQL is currently the recommended pattern.

Why How to Drop All Tables in Snowflake is important

How to Drop All Tables in Snowflake Example Usage


--Assume an ecommerce schema PUBLIC in database SHOP_DB
EXECUTE IMMEDIATE (
  SELECT LISTAGG('DROP TABLE "' || table_schema || '"."' || table_name || '";', ' ')
  FROM SHOP_DB.INFORMATION_SCHEMA.TABLES
  WHERE table_schema = 'PUBLIC'
);
--This drops Customers, Orders, Products, and OrderItems in one call.

How to Drop All Tables in Snowflake Syntax


--Generate DROP statements for all tables in a schema
SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '";' AS drop_stmt
FROM MYDB.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
  AND table_schema = 'PUBLIC';

--Aggregate and execute them
EXECUTE IMMEDIATE (
  SELECT LISTAGG(drop_stmt, ' ')
  FROM (
      SELECT 'DROP TABLE "' || table_schema || '"."' || table_name || '";' AS drop_stmt
      FROM MYDB.INFORMATION_SCHEMA.TABLES
      WHERE table_schema = 'PUBLIC'
  )
);

Common Mistakes

Omitting the schema filter causes tables in every schema to be deleted. Always add WHERE table_schema='YOUR_SCHEMA' to limit scope.
Running as a role without OWNERSHIP results in partial drops and confusing errors. Grant the role OWNERSHIP on tables or switch to SYSADMIN before execution.