Export (0) Print
Expand All

Delete Tables (Database Engine)

You can delete (drop) a table from your database in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

Caution note 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

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.

Arrow icon used with Back to Top link [Top]

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 Note

    Deleting a table automatically removes any relationships to it.

Arrow icon used with Back to Top link [Top]

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)

Arrow icon used with Back to Top link [Top]

Community Additions

ADD
Show:
© 2014 Microsoft