Delete Tables (Database Engine)
Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).
You can delete (drop) a table from your database in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL.
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
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.
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.
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.
Deleting a table automatically removes any relationships to it.
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)