> ## 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 TABLE The `DROP TABLE` [statement](/docs/stable/sql-statements.md) removes a table and all its indexes from a database. > [!NOTE] > > The `DROP TABLE` 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 specified table(s). If `CASCADE` is used, the user must have the privileges required to drop each dependent object as well. ## Synopsis ![drop\_table syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_table.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=5645e75ee9ab4241c0e8a81c62b5facb) ## 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 [`SHOW TABLES`](/docs/stable/show-tables). | | `CASCADE` | Drop all objects (such as [constraints](/docs/stable/constraints.md) and [views](/docs/stable/views.md)) 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 [constraints](/docs/stable/constraints.md) and [views](/docs/stable/views.md)) depend on it. | ## 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 ``` ### 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; ``` ``` 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; ``` ``` DROP TABLE ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM movr; ``` ``` 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 [foreign key](/docs/stable/foreign-key.md) 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`. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM movr; ``` ``` 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; ``` ``` pq: "users" is referenced by foreign key from table "vehicles" ``` To see how `users` is referenced from `vehicles`, you can use the [`SHOW CREATE`](/docs/stable/show-create) 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; ``` ``` 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; ``` ``` DROP TABLE ``` ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW TABLES FROM movr; ``` ``` 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; ``` ``` 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 * [`ALTER TABLE`](/docs/stable/alter-table) * [`CREATE TABLE`](/docs/stable/create-table) * [`INSERT`](/docs/stable/insert) * [`ALTER TABLE ... RENAME TO`](/docs/stable/alter-table#rename-to) * [`SHOW COLUMNS`](/docs/stable/show-columns) * [`SHOW TABLES`](/docs/stable/show-tables) * [`UPDATE`](/docs/stable/update) * [`DELETE`](/docs/stable/delete) * [`DROP INDEX`](/docs/stable/drop-index) * [`DROP VIEW`](/docs/stable/drop-view) * [`SHOW JOBS`](/docs/stable/show-jobs) * [Online Schema Changes](/docs/stable/online-schema-changes.md)