Delete Tables (Database Engine)
Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
You can delete (drop) a table from your database in SQL Server by using SQL Server Management Studio or Transact-SQL.
Caution
Think carefully before you delete a table. If existing queries, views, user-defined functions, stored procedures, or programs refer to that table, the deletion will make these objects invalid.
In This Topic
Before you begin:
To Delete a Table, using:
Before You Begin
Limitations and Restrictions
You cannot drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.
When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.
If you drop a table that contains a varbinary (max) column with the FILESTREAM attribute, any data stored in the file system will not be removed.
DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.
Any view or stored procedure that references the dropped table must be explicitly deleted or modified to remove the reference to the table.
Security
Permissions
Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.
Using SQL Server Management Studio
To delete a table from the database
In Object Explorer, select the table you want to delete.
Right-click the table and choose Delete from the shortcut menu.
A message box prompts you to confirm the deletion. Click Yes.
Note
Deleting a table automatically removes any relationships to it.
Using Transact-SQL
To delete a table in Query Editor
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
DROP TABLE dbo.PurchaseOrderDetail;
For more information, see DROP TABLE (Transact-SQL)