> ## 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 DATABASE

The `DROP DATABASE` [statement](/docs/stable/sql-statements.md) removes a database and all its objects from a CockroachDB cluster.

> [!NOTE]
>
> The `DROP DATABASE` 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 database and on all tables in the database.

## Synopsis

![drop\_database syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_database.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=5583ef4d5eae2d58cf6c5c8146010fc9)

## Parameters

| Parameter | Description |
| - | - |
| `IF EXISTS` | Drop the database if it exists; if it does not exist, do not return an error. |
| `name` | The name of the database you want to drop. You cannot drop a database if it is set as the [current database](/docs/stable/sql-name-resolution.md#current-database) or if [`sql_safe_updates = true`](/docs/stable/set-vars). |
| `CASCADE` | *(Default)* Drop all tables and views in the database 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` | Do not drop the database if it contains any [tables](/docs/stable/create-table.md) or [views](/docs/stable/create-view.md). |

## Viewing schema changes

This schema change statement is registered as a job.  You can view long-running jobs with [`SHOW JOBS`](/docs/stable/show-jobs).

## 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 database and its objects (`CASCADE`)

For non-interactive sessions (e.g., client applications), `DROP DATABASE` applies the `CASCADE` option by default, which drops all tables and views in the database as well as all objects (such as [constraints](/docs/stable/constraints.md) and [views](/docs/stable/views.md)) that depend on those tables.

For interactive sessions from the [built-in SQL client](/docs/stable/cockroach-sql.md), either the `CASCADE` option must be set explicitly or the `--unsafe-updates` flag must be set when starting the shell.

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

```
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
```

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

```
ERROR: rejected (sql_safe_updates = true): DROP DATABASE on current database
SQLSTATE: 01000
```

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

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

```
ERROR: rejected (sql_safe_updates = true): DROP DATABASE on non-empty database without explicit CASCADE
SQLSTATE: 01000
```

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

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

```
ERROR: target database or schema does not exist
SQLSTATE: 3F000
```

### Prevent dropping a non-empty database (`RESTRICT`)

When a database is not empty, the `RESTRICT` option prevents the database from being dropped:

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

```
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | promo_codes                | table |                1000
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(6 rows)
```

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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> DROP DATABASE movr RESTRICT;
```

```
ERROR: database "movr" is not empty and RESTRICT was specified
SQLSTATE: 2BP01
```

## See also

* [`CREATE DATABASE`](/docs/stable/create-database)
* [`SHOW DATABASES`](/docs/stable/show-databases)
* [`ALTER DATABASE ... RENAME TO`](/docs/stable/alter-database#rename-to)
* [`SET DATABASE`](/docs/stable/set-vars)
* [`SHOW JOBS`](/docs/stable/show-jobs)
* [SQL Statements](/docs/stable/sql-statements.md)
* [Online Schema Changes](/docs/stable/online-schema-changes.md)