SQL `DROP SCHEMA` Tutorial

Table of contents

DROP SCHEMA - Drop an empty schema.
DROP SCHEMA IF EXISTS - Check that a schema exists before dropping it.
DROP SCHEMA CASCADE - Drop a non-empty schema (PostgreSQL only).
Dropping schemas in Oracle - Explanation of how Oracle handles schemas differently.

`DROP SCHEMA`

Works in PostgreSQL, MySQL, and SQL Server.
Throws an error if the schema isn’t empty.

DROP SCHEMA schema_name;

`DROP SCHEMA IF EXISTS`

Works in PostgreSQL, MySQL, and SQL Server.
Only attempts to drop the schema if it exists.
Useful for cleaning up logs by avoiding unnecessary errors.

DROP SCHEMA IF EXISTS schema_name;

`DROP SCHEMA CASCADE`

PostgreSQL only.
Drops the schema and any tables inside it.
It is not possible to drop a non-empty schema in MySQL or SQL Server.

DROP SCHEMA schema_name CASCADE;

Oracle `DROP SCHEMA`

Oracle only.
Schemas are generally synonymous with users in Oracle.
Dropping a schema in Oracle usually means dropping the user.
Link to Oracle’s DROP USER documentation.

DROP USER user_name; -- Drops user if their schema is empty

DROP USER user_name CASCADE; -- Drops user and all tables in their schema