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

# DROP TABLE

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `DROP TABLE` <InternalLink path="sql-statements">statement</InternalLink> removes a table and all its indexes from a database.

The `DROP TABLE` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>.

## Required privileges

The user must have the `DROP` <InternalLink path="security-reference/authorization#managing-privileges">privilege</InternalLink> on the specified table(s). If `CASCADE` is used, the user must have the privileges required to drop each dependent object as well.

## Synopsis

<img src="https://mintcdn.com/cockroachlabs/Bc-7BE4092mn9J_1/images/sql-diagrams/v23.2/drop_table.svg?fit=max&auto=format&n=Bc-7BE4092mn9J_1&q=85&s=733ddae867bc442604ca762fb8fc50f0" alt="drop_table syntax diagram" style={{maxWidth: "100%", overflowX: "auto"}} width="673" height="113" data-path="images/sql-diagrams/v23.2/drop_table.svg" />

## Parameters

| Parameter           | Description                                                                                                                                                                                                                                   |
| ------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `IF EXISTS`         | Drop the table if it exists; if it does not exist, do not return an error.                                                                                                                                                                    |
| `table\_name\_list` | A comma-separated list of table names. To find table names, use <InternalLink path="show-tables">`SHOW TABLES`</InternalLink>.                                                                                                                |
| `CASCADE`           | Drop all objects (such as <InternalLink path="constraints">constraints</InternalLink> and <InternalLink path="views">views</InternalLink>) that depend on the table.  `CASCADE` does not list objects it drops, so should be used cautiously. |
| `RESTRICT`          | *(Default)* Do not drop the table if any objects (such as <InternalLink path="constraints">constraints</InternalLink> and <InternalLink path="views">views</InternalLink>) depend on it.                                                      |

## Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink>.

## Examples

#### Setup

To follow along, run <InternalLink path="cockroach-demo">`cockroach demo`</InternalLink> to start a temporary, in-memory cluster with the <InternalLink path="movr">`movr`</InternalLink> sample dataset preloaded:

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

### Remove a table (no dependencies)

In this example, other objects do not depend on the table being dropped.

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  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 TABLE promo_codes;
```

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP TABLE
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(5 rows)
```

### Remove a table and dependent objects with `CASCADE`

In this example, a <InternalLink path="foreign-key">foreign key</InternalLink> from a different table references the table being dropped. Therefore, it's only possible to drop the table while simultaneously dropping the dependent foreign key constraint using `CASCADE`.

`CASCADE` drops *all* dependent objects without listing them, which can lead to inadvertent and difficult-to-recover losses. To avoid potential harm, we recommend dropping objects individually in most cases.

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | users                      | table |                  50
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(5 rows)
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
pq: "users" is referenced by foreign key from table "vehicles"
```

To see how `users` is referenced from `vehicles`, you can use the <InternalLink path="show-create">`SHOW CREATE`</InternalLink> statement. `SHOW CREATE` shows how the columns in a table are created, including data types, default values, indexes, and constraints.

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |                                         create_statement
-------------+---------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE public.vehicles (

             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT vehicles_pkey PRIMARY KEY (city ASC, id ASC),
             |     CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES public.users(city, id),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | )
(1 row)
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DROP TABLE
```

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  schema_name |         table_name         | type  | estimated_row_count
--------------+----------------------------+-------+----------------------
  public      | rides                      | table |                 500
  public      | user_promo_codes           | table |                   0
  public      | vehicle_location_histories | table |                1000
  public      | vehicles                   | table |                  15
(4 rows)
```

Use a `SHOW CREATE TABLE` statement to verify that the foreign key constraint has been removed from `vehicles`.

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
  table_name |                                       create_statement
-------------+------------------------------------------------------------------------------------------------
  vehicles   | CREATE TABLE public.vehicles (
             |     id UUID NOT NULL,
             |     city VARCHAR NOT NULL,
             |     type VARCHAR NULL,
             |     owner_id UUID NULL,
             |     creation_time TIMESTAMP NULL,
             |     status VARCHAR NULL,
             |     current_location VARCHAR NULL,
             |     ext JSONB NULL,
             |     CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC),
             |     INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC),
             |     FAMILY "primary" (id, city, type, owner_id, creation_time, status, current_location, ext)
             | )
(1 row)
```

## See also

* <InternalLink path="alter-table">`ALTER TABLE`</InternalLink>
* <InternalLink path="create-table">`CREATE TABLE`</InternalLink>
* <InternalLink path="insert">`INSERT`</InternalLink>
* <InternalLink path="alter-table#rename-to">`ALTER TABLE... RENAME TO`</InternalLink>
* <InternalLink path="show-columns">`SHOW COLUMNS`</InternalLink>
* <InternalLink path="show-tables">`SHOW TABLES`</InternalLink>
* <InternalLink path="update">`UPDATE`</InternalLink>
* <InternalLink path="delete">`DELETE`</InternalLink>
* <InternalLink path="drop-index">`DROP INDEX`</InternalLink>
* <InternalLink path="drop-view">`DROP VIEW`</InternalLink>
* <InternalLink path="show-jobs">`SHOW JOBS`</InternalLink>
* <InternalLink path="online-schema-changes">Online Schema Changes</InternalLink>