PostgreSQL

DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP;
END $$;

This query will delete all available tables for current user and schema, without needs to have SCHEMA privileges.

The most popular solution via:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Often shows the error, because of missing owner permissions or other restrictions: