DROP SCHEMA

Syntax

DROP SCHEMA schema_name;

Description

Drops a schema from the current database. Only the schema owner can execute this command. Also, you cannot drop a schema if it contains any relations.

Important 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. If the name is case sensitive you must wrap it in double quotes; otherwise the system uses the lower case name.

Examples

Drop a schema in the current database

The following drops the schema in the current database demodb:

demodb.public/demostore# show SCHEMAS;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-07-02 16:23:26 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| otherdb    | false       | sysadmin   | 2024-07-02 21:43:27 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| newschema  | false       | sysadmin   | 2024-07-03 15:32:21 +0000 UTC |
+------------+-------------+------------+-------------------------------+
demodb.public/demostore# DROP SCHEMA newschema;
+------------+------------------+------------+------------------------------------------+
|  Type      |  Name            |  Command   |  Summary                                 |
+============+==================+============+==========================================+
| schema     | demodb.newschema | DROP       | schema "demodb.newschema" was            |
|            |                  |            | successfully dropped                     |
+------------+------------------+------------+------------------------------------------+
demodb.public/<no-store># LIST SCHEMAS;
+------------+-------------+------------+-------------------------------+
|  Name      |  Is Default |  Owner     |  Created At                   |
+============+=============+============+===============================+
| public     | true        | sysadmin   | 2024-07-02 16:23:26 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| otherdb    | false       | sysadmin   | 2024-07-02 21:43:27 +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                   |
+============+=============+============+===============================+
| S1         | false       | sysadmin   | 2024-07-02 21:44:59 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| public     | true        | sysadmin   | 2024-07-02 21:21:18 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| NewSchema  | false       | sysadmin   | 2024-07-02 21:21:25 +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                   |
+============+=============+============+===============================+
| S1         | false       | sysadmin   | 2024-07-02 21:44:59 +0000 UTC |
+------------+-------------+------------+-------------------------------+
| public     | true        | sysadmin   | 2024-07-02 21:21:18 +0000 UTC |
+------------+-------------+------------+-------------------------------+