This article shows how to drop a table in a SQL Server database. The T-SQL statement DROP TABLE removes all rows from a table, and the structure of the table. To remove all data and objects owned by a user, use the DROP USER statement.

To drop a table you must have the DROP permission on the table to drop it. You cannot drop a table that is referenced by a FOREIGN KEY constraint. To drop a table that is referenced by a FOREIGN KEY constraint, you must first drop the foreign key constraint. Also you cannot drop a table if it is currently in use.

Syntax

DROP TABLE [ IF EXISTS ] table_name;

Example

CREATE TABLE test(id int, ename varchar(50));
DROP TABLE test;

Drop table using IF EXISTS

The following example creates a table in SQL Server database, tests for its existence with IF EXISTS and then drop the table.

CREATE TABLE test(id int, ename varchar(50));
DROP TABLE IF EXISTS test;

Drop multiple tables

The following example show how to drop multiple tables in SQL Server database:

CREATE TABLE test1(id int, ename varchar(50));
CREATE TABLE test2(id int, ename varchar(50));
CREATE TABLE test3(id int, ename varchar(50));
DROP TABLE test1, test2, test3;

Drop multiple tables using IF EXISTS

When you want to drop multiple tables from the SQL Server database, it is safe to use IF EXISTS to check the existence of the table and avoid possible errors generated if a table does not exist in the database.

CREATE TABLE test1(id int, ename varchar(50));
CREATE TABLE test3(id int, ename varchar(50));
DROP TABLE IF EXISTS test1, test2, test3;

Drop table using OBJECT_ID

The following example show how to drop a table with OBJECT_ID() metadata function.

CREATE TABLE test(id int, ename varchar(50));
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
DROP TABLE dbo.test;

Drop a temporary table

If you wanted to drop a temporary table before the session ends, you would use the following T-SQL statement:
CREATE TABLE #temptable(id INT);
GO
DROP TABLE IF EXISTS #temptable;
GO