> ## 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](https://docs.cockroachlabs.com/docs/v24.2/sql-statements) 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](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 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](https://docs.cockroachlabs.com/docs/v24.2/sql-name-resolution#current-database) or if [`sql_safe_updates = true`](https://docs.cockroachlabs.com/docs/v24.2/set-vars). | | `CASCADE` | *(Default)* Drop all tables and views in the database 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` | Do not drop the database 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). | ## Viewing schema changes This schema change statement is registered as a job. You can view long-running jobs with [`SHOW JOBS`](https://docs.cockroachlabs.com/docs/v24.2/show-jobs). ## 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 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](https://docs.cockroachlabs.com/docs/v24.2/constraints) and [views](https://docs.cockroachlabs.com/docs/v24.2/views)) that depend on those tables. For interactive sessions from the [built-in SQL client](https://docs.cockroachlabs.com/docs/v24.2/cockroach-sql), 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`](https://docs.cockroachlabs.com/docs/v24.2/create-database) * [`SHOW DATABASES`](https://docs.cockroachlabs.com/docs/v24.2/show-databases) * [`ALTER DATABASE ... RENAME TO`](https://docs.cockroachlabs.com/docs/v24.2/alter-database#rename-to) * [`SET DATABASE`](https://docs.cockroachlabs.com/docs/v24.2/set-vars) * [`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)