DROP SCHEMA

Syntax

DROP SCHEMA schema_name;

Description

Drops a Schema from the current Database. It can only be executed by the Schema owner. A Schema cannot be dropped if it contains any relations.

DROP SCHEMA cannot be undone. Use it with care!

Arguments

schema_name

The name of the Schema to drop. Optionally, a fully qualified Schema name can be provided to drop a Schema from a Database other than the current Database. For case-sensitive names, the name must be wrapped in double quotes, otherwise, the lowercased name will be used.

Examples

Drop a Schema in the current Database

The following drops the Schema in the current Database demodb:

demodb.public/<no-store># LIST SCHEMAS;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-05-09 22:02:29 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| other      | false       | sysadmin   | 2024-05-09 22:14:05 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| newschema  | false       | sysadmin   | 2024-05-09 22:02:41 +0000 UTC |
+------------+-------------+------------+-------------------------------+
demodb.public/<no-store># DROP SCHEMA other;
+------------+--------------+------------+------------------------------------------+
|  Type      |  Name        |  Command   |  Summary                                 |
+============+==============+============+==========================================+
| schema     | demodb.other | DROP       | schema "demodb.other" was successfully   |
|            |              |            | dropped                                  |
+------------+--------------+------------+------------------------------------------+
demodb.public/<no-store># LIST SCHEMAS;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-05-09 22:02:29 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| newschema  | false       | sysadmin   | 2024-05-09 22:02:41 +0000 UTC |
+------------+-------------+------------+-------------------------------+

Drop the current Schema in the current Database

The following drops the current Schema newschema in the current Database demodb. Notice how the current Schema was wiped, where a new Schema can be set for the current sessions using the USE command:

demodb.newschema/<no-store># DROP SCHEMA newschema;
+------------+------------------+------------+------------------------------------------+
|  Type      |  Name            |  Command   |  Summary                                 |
+============+==================+============+==========================================+
| schema     | demodb.newschema | DROP       | schema "demodb.newschema" was            |
|            |                  |            | successfully dropped                     |
+------------+------------------+------------+------------------------------------------+
demodb.<no-schema>/<no-store>#

Drop the default Schema in the current Database

The following example shows that when dropping the default Schema public, the default Schema is also wiped from the Database demodb. Use the SET DEFAULT command to set a new one for the Database from LIST SCHEMAS:

demodb.public/<no-store># DROP SCHEMA "public";
+------------+---------------+------------+------------------------------------------+
|  Type      |  Name         |  Command   |  Summary                                 |
+============+===============+============+==========================================+
| schema     | demodb.public | DROP       | schema "demodb.public" was successfully  |
|            |               |            | dropped                                  |
+------------+---------------+------------+------------------------------------------+
demodb.<no-schema>/<no-store># LIST SCHEMAS;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| my_schema  | false       | sysadmin   | 2024-05-09 22:22:06 +0000 UTC |
+------------+-------------+------------+-------------------------------+

Drop a Schema in a specific Database

The following drops the Schema NewSchema in the specified Database otherdb:

demodb.public/<no-store># LIST SCHEMAS IN DATABASE otherdb;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-05-09 21:56:57 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| NewSchema  | false       | sysadmin   | 2024-05-09 22:03:45 +0000 UTC |
+------------+-------------+------------+-------------------------------+
demodb.public/<no-store># DROP SCHEMA otherdb."NewSchema";
+------------+-------------------+------------+------------------------------------------+
|  Type      |  Name             |  Command   |  Summary                                 |
+============+===================+============+==========================================+
| schema     | otherdb.NewSchema | DROP       | schema "otherdb.NewSchema" was           |
|            |                   |            | successfully dropped                     |
+------------+-------------------+------------+------------------------------------------+
demodb.public/<no-store># LIST SCHEMAS IN DATABASE otherdb;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-05-09 21:56:57 +0000 UTC |
+------------+-------------+------------+-------------------------------+