Database

Drop all tables in a PostgreSQL schema


Execute the following query to drop all tables in a given schema. Replace my-schema-name with the name of your schema. In Supabase, the default schema is public.


_10
do $$ declare
_10
r record;
_10
begin
_10
for r in (select tablename from pg_tables where schemaname = 'my-schema-name') loop
_10
execute 'drop table if exists ' || quote_ident(r.tablename) || ' cascade';
_10
end loop;
_10
end $$;

This query works by listing out all the tables in the given schema and then executing a drop table for each (hence the for... loop).

You can run this query using the SQL Editor in the Supabase Dashboard, or via psql if you're connecting directly to the database.