Removes one or more tables from a database.

Syntax


DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name [ ,...n ]
    [ ; ]

Arguments

database_name
The name of the database containing the table to be dropped.
schema_name
The name of the schema to which the table belongs.
table_name
The name of the table to be dropped. If the table does not exist or is not visible to the user, an error is raised.

Permissions

Requires ALTER permission on the schema in which the table is created, or a permission that bypasses this check, such as membership in the db_ddladmin fixed database role.

Examples

A. Dropping a single table

The following example drops the table named Production.ProductReview.


DROP TABLE Production.ProductReview;

B. Dropping multiple tables

The following example drops two tables: Sales.SalesPersonQuotaHistory and Sales.SalesPerson.


DROP TABLE Sales.SalesPersonQuotaHistory, Sales.SalesPerson;

Remarks

  • DROP TABLE can be used to drop one or more tables at a time.
  • If you drop a table that is referenced by a FOREIGN KEY constraint, you must drop the constraint first. If you attempt to drop a table with an existing FOREIGN KEY constraint, an error will occur.
  • You can drop a table that has an INDEX, but you cannot drop an index by using DROP TABLE.
  • DROP TABLE can be used in a transaction.
  • If a table is referenced by a view or stored procedure, the view or stored procedure will become invalid when the table is dropped.

Note

When you drop a table, all data and all information about the table are removed from the database. You cannot recover the table unless you have a backup.

Warning

Use DROP TABLE with extreme caution. Dropping a table permanently deletes it and all its contents. Always verify the table name before executing the command.

Applies to

  • SQL Server 2022
  • SQL Server 2019
  • SQL Server 2017
  • SQL Server 2016
  • SQL Server 2014
  • SQL Server 2012
  • SQL Server 2008 R2
  • SQL Server 2008
  • SQL Server 2005
  • Azure SQL Database
  • Azure Synapse Analytics

See also