Edit

Delete Tables (Database Engine)

  • Article

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

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

  1. In Object Explorer, select the table you want to delete.

  2. Right-click the table and choose Delete from the shortcut menu.

  3. 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

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. 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)