How to Drop All Tables in Redshift

How do I drop all tables in an Amazon Redshift schema?

Generate and execute dynamic DROP TABLE statements to remove every table in an Amazon Redshift schema.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

How can I drop all tables in a Redshift schema quickly?

Create SQL that iterates through pg_table_def, builds one DROP TABLE IF EXISTS statement per table, and executes those statements in a single transaction.

What dynamic SQL script should I use?

BEGIN; -- build drop statements SELECT 'DROP TABLE IF EXISTS "' || schemaname || '"."' || tablename || '" CASCADE;' AS stmt INTO TEMP TABLE drop_cmds FROM pg_table_def WHERE schemaname = 'public' GROUP BY schemaname, tablename; -- run every drop DO $$ DECLARE r RECORD; BEGIN FOR r IN SELECT stmt FROM drop_cmds LOOP EXECUTE r.stmt; END LOOP; END $$; COMMIT;

Why use IF EXISTS and CASCADE?

IF EXISTS prevents errors if a table was removed mid-script. CASCADE removes dependent objects like views or foreign keys, ensuring a clean wipe.

Can I restrict the drop to certain tables?

Yes – add filters in the WHERE clause (e.g., tablename LIKE 'tmp_%') or exclude critical tables with AND tablename NOT IN ('customers','orders').

What permissions are required?

The user running the script must own the tables or have superuser privileges. Without ownership, each DROP will fail with an insufficient privilege error.

Best practice: run in a transaction

Wrapping the script in BEGIN…COMMIT lets you roll back if something goes wrong, preserving data safety during development.

Best practice: back up first

Export the schema or take a snapshot before dropping to avoid accidental, irreversible data loss.

Why How to Drop All Tables in Redshift is important

How to Drop All Tables in Redshift Example Usage


-- Remove every table in the reporting schema
do $$
declare cmd text;
begin
  for cmd in
      select format('DROP TABLE IF EXISTS reporting.%I CASCADE;', tablename)
      from pg_table_def
      where schemaname = 'reporting'
      group by tablename
  loop
      execute cmd;
  end loop;
end $$;

How to Drop All Tables in Redshift Syntax


DROP TABLE [IF EXISTS] <schema_name>.<table_name> [CASCADE];

Common Mistakes

Running individual DROP statements manually. This is slow and error-prone. Fix by generating statements from pg_table_def and executing them in a loop.
Omitting CASCADE when tables have dependencies. Redshift raises errors and stops the script. Add CASCADE or drop dependent objects first.