DROP SCHEMA

The DROP SCHEMA removes a user-defined .

The “ statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

Required privileges

The user must have the DROP 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

Parameters

Parameter	Description
IF EXISTS	Drop the schema if it exists. If it does not exist, do not return an error.
schema_name_list	The schema, or a list of schemas, that you want to drop. To drop a schema in a database other than the current database, specify the name of the database and the name of the schema, separated by a “.” (e.g., DROP SCHEMA IF EXISTS database.schema;).
CASCADE	Drop all tables and views in the schema as well as all objects (such as and ) 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 or .

Examples

Setup

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

$ cockroach demo

Drop a schema

> CREATE SCHEMA org_one;

> SHOW SCHEMAS;

     schema_name
----------------------
  crdb_internal
  information_schema
  org_one
  pg_catalog
  pg_extension
  public
(6 rows)

> DROP SCHEMA org_one;

> 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.

> CREATE SCHEMA org_two;

> SHOW SCHEMAS;

     schema_name
----------------------
  crdb_internal
  information_schema
  org_two
  pg_catalog
  pg_extension
  public
(6 rows)

> CREATE TABLE org_two.users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        city STRING,
        name STRING,
        address STRING
);

> SHOW TABLES FROM org_two;

  schema_name | table_name | type  | estimated_row_count
--------------+------------+-------+----------------------
  org_two     | users      | table |                   0
(1 row)

> DROP SCHEMA org_two;

ERROR: schema "org_two" is not empty and CASCADE was not specified
SQLSTATE: 2BP01

> DROP SCHEMA org_two CASCADE;

> SHOW SCHEMAS;

     schema_name
----------------------
  crdb_internal
  information_schema
  pg_catalog
  pg_extension
  public
(5 rows)

See also