Updated: October 28, 2015

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. To report the dependencies on a table, use sys.dm_sql_referencing_entities.

Topic link icon Transact-SQL Syntax Conventions

-- Syntax for SQL Server and Azure SQL Database  
DROP TABLE [ IF EXISTS ] [ database_name . [ schema_name ] . | schema_name . ]  
table_name [ ,...n ]  
[ ; ]  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
DROP TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name   

Is the name of the database in which the table was created.

Windows Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #. Windows Azure SQL Database does not support four-part names.


Applies to: SQL Server (SQL Server 2016 through current version).

Conditionally drops the table only if it already exists.

Is the name of the schema to which the table belongs.

Is the name of the table to be removed.

DROP TABLE cannot be used to 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.

Multiple tables can be dropped in any database. If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

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 delete all rows in a table by using DELETE tablename or use the TRUNCATE TABLE statement, the table exists until it is dropped.

Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

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.

System_CAPS_ICON_important.jpg Important

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

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.

A. Dropping a table in the current database

The following example removes the ProductVendor1 table and its data and indexes from the current database.

DROP TABLE ProductVendor1 ;  

B. Dropping a table in another database

The following example drops the SalesPerson2 table in the AdventureWorks2012 database. The example can be executed from any database on the server instance.

DROP TABLE AdventureWorks2012.dbo.SalesPerson2 ;  

C. Dropping a temporary table

The following example creates a temporary table, tests for its existence, drops it, and tests again for its existence. This example does not use the IF EXISTS syntax which is available beginning with SQL Server 2016.

CREATE TABLE #temptable (col1 int);  
INSERT INTO #temptable  
VALUES (10);  
SELECT * FROM #temptable;  
IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL   
DROP TABLE #temptable;  
--Test the drop.  
SELECT * FROM #temptable;  

D. Dropping a table using IF EXISTS

Applies to: SQL Server (SQL Server 2016 through current version).

The following example creates a table named T1. Then the second statement drops the table. The third statement performs no action because the table is already deleted, however it does not cause an error.

CREATE TABLE T1 (Col1 int);  

E. Using basic syntax

DROP TABLE SalesPerson;  
DROP TABLE dbo.SalesPerson;  
DROP TABLE EasternDivision.dbo.SalesPerson;  

F. Dropping a table from the current database

The following example removes the ProductVendor1 table, its data, indexes, and any dependent views from the current database.

DROP TABLE ProductVendor1;  

G. Dropping a table from another database

The following example drops the SalesPerson table in the EasternDivision database.

DROP TABLE EasternDivision.dbo.SalesPerson;  

DELETE (Transact-SQL)
sp_help (Transact-SQL)
sp_spaceused (Transact-SQL)
DROP VIEW (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)

Community Additions