SQL Server - DROP TABLE

The SQL Server (Transact-SQL) DROP TABLE statement is used to delete a table from the database. It drops all the data, indexes, triggers, constraints and permission specifications for the specified table.

Note: Be careful before dropping a table. Once deleted, all information stored in that table will be lost forever!.

Syntax

The syntax of using DROP TABLE statement in SQL Server (Transact-SQL) is given below:

DROP TABLE [IF EXISTS] table_name;

The IF EXISTS is an optional parameter that conditionally drops table only if it exists on the database. If a table is deleted which does not exist, it will raise an error.

Example:

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The table can be checked using EXEC sp_help command as shown below:

EXEC sp_help Employee;

This will produce the result as shown below:

Name	Owner	Type	Created_datetime
Employee	dbo	user table	2019-10-21 10:18:45.477

To delete this table, the query is given below:

DROP TABLE Employee;

After dropping the table, the EXEC sp_help command will throw following error:

EXEC sp_help Employee;	
Result: The object 'Employee' does not exist in database 'testDB' or is invalid for this operation.

Here, testDB is the name of database which initially had Employee table.