DROP TABLE (Transact-SQL)

Removes one or more tables from a database.

Syntax

DROP TABLE
    [ database_name.schema_name.table_name -- Applies to: SQL Server 2008 and later
    | schema_name.table_name
    | table_name
    ]
[ ,...n ]
[ ; ]

Description

DROP TABLE removes the definition of a table and all its data. If any views or stored procedures reference the table, the DROP TABLE statement will fail unless those objects are dropped or modified first.

Note

You can use DROP TABLE to drop multiple tables with a single statement. However, if you try to drop a table that does not exist, an error will be raised.

Permissions

DROP TABLE requires ALTER permission on the schema that contains the table or db_ddladmin database role membership.

Examples

A. Dropping a single table

The following example drops the table named ProductVendors.

DROP TABLE ProductVendors;

B. Dropping multiple tables

The following example drops two tables: Products and ProductCategories.

DROP TABLE Products, ProductCategories;

C. Dropping a table with schema and database names

The following example drops the table Sales.Customers in the AdventureWorks database.

DROP TABLE AdventureWorks.Sales.Customers;