> ## 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](/docs/stable/sql-statements.md) removes a user-defined [schema](/docs/stable/sql-name-resolution.md#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](/docs/stable/online-schema-changes.md).

## Required privileges

The user must have the `DROP` [privilege](/docs/stable/security-reference/authorization.md#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](/docs/stable/constraints.md) and [views](/docs/stable/views.md)) 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](/docs/stable/create-table.md) or [views](/docs/stable/create-view.md). |

## Examples

#### Setup

To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/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`](/docs/stable/create-schema)
* [`SHOW SCHEMAS`](/docs/stable/show-schemas)
* [`SHOW JOBS`](/docs/stable/show-jobs)
* [SQL Statements](/docs/stable/sql-statements.md)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)