> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DROP SCHEMA

The `DROP SCHEMA` [statement](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) removes a user-defined [schema](https://docs.cockroachlabs.com/docs/v24.2/sql-name-resolution#naming-hierarchy).

> [!NOTE]
>
> The `DROP SCHEMA` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes).

## Required privileges

The user must have the `DROP` [privilege](https://docs.cockroachlabs.com/docs/v24.2/security-reference/authorization#managing-privileges) 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 syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_schema.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=8f46b33d267446bb5f0c14ca0680b6c7)

### 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 [constraints](https://docs.cockroachlabs.com/docs/v24.2/constraints) and [views](https://docs.cockroachlabs.com/docs/v24.2/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](https://docs.cockroachlabs.com/docs/v24.2/create-table) or [views](https://docs.cockroachlabs.com/docs/v24.2/create-view). |

## Examples

#### Setup

To follow along, run [`cockroach demo`](https://docs.cockroachlabs.com/docs/v24.2/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](https://docs.cockroachlabs.com/docs/v24.2/movr) sample dataset preloaded:

```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```

### Drop a schema

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA org_one;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP SCHEMA org_one;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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.

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE SCHEMA org_two;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE org_two.users (
        id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
        city STRING,
        name STRING,
        address STRING
);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW TABLES FROM org_two;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP SCHEMA org_two;
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP SCHEMA org_two CASCADE;
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW SCHEMAS;
```

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

## See also

* [`CREATE SCHEMA`](https://docs.cockroachlabs.com/docs/v24.2/create-schema)
* [`SHOW SCHEMAS`](https://docs.cockroachlabs.com/docs/v24.2/show-schemas)
* [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs)
* [SQL Statements](https://docs.cockroachlabs.com/docs/v24.2/sql-statements)
* [Online Schema Changes](https://docs.cockroachlabs.com/docs/v24.2/online-schema-changes)
