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