DROP SCHEMA

Warning:
Cockroach Labs will stop providing Assistance Support for this version on May 10, 2022. Prior to that date, upgrade to a more recent version to continue receiving support. For more details, see the Release Support Policy.

New in v20.2: The DROP SCHEMA statement removes a user-defined schema from the current database.

Required privileges

The user must have the DROP privilege on the schema and on all tables in the schema. If the user is the owner of the schema, DROP privileges are not necessary.

Syntax

DROP SCHEMA IF EXISTS name_list CASCADE RESTRICT

Parameters

Parameter Description
IF EXISTS Drop the schema if it exists. If it does not exist, do not return an error.
name_list The schema, or a list of schemas, that you want to drop.
CASCADE Drop all tables and views in the schema as well as all objects (such as constraints and views) that depend on those tables.

CASCADE does not list objects it drops, so should be used cautiously.
RESTRICT (Default) Do not drop the schema if it contains any tables or views.

Examples

Setup

The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr dataset preloaded:

icon/buttons/copy
$ cockroach demo

Drop a schema

icon/buttons/copy
> CREATE SCHEMA org_one;
icon/buttons/copy
> SHOW SCHEMAS;
     schema_name
----------------------
  crdb_internal
  information_schema
  org_one
  pg_catalog
  pg_extension
  public
(6 rows)
icon/buttons/copy
> DROP SCHEMA org_one;
icon/buttons/copy
> SHOW SCHEMAS;
     schema_name
----------------------
  crdb_internal
  information_schema
  pg_catalog
  pg_extension
  public
(5 rows)

Drop a schema with tables

To drop a schema that contains tables, you need to use the CASCADE keyword.

icon/buttons/copy
> CREATE SCHEMA org_two;
icon/buttons/copy
> SHOW SCHEMAS;
     schema_name
----------------------
  crdb_internal
  information_schema
  org_two
  pg_catalog
  pg_extension
  public
(6 rows)
icon/buttons/copy
> CREATE TABLE org_two.users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        city STRING,
        name STRING,
        address STRING
);
icon/buttons/copy
> SHOW TABLES FROM org_two;
  schema_name | table_name | type  | estimated_row_count
--------------+------------+-------+----------------------
  org_two     | users      | table |                   0
(1 row)
icon/buttons/copy
> DROP SCHEMA org_two;
ERROR: schema "org_two" is not empty and CASCADE was not specified
SQLSTATE: 2BP01
icon/buttons/copy
> DROP SCHEMA org_two CASCADE;
icon/buttons/copy
> SHOW SCHEMAS;
     schema_name
----------------------
  crdb_internal
  information_schema
  pg_catalog
  pg_extension
  public
(5 rows)

See also

YesYes NoNo