Drop Table in SQL Server

in SQL Server we use the drop table statement to delete a table & data stored in it from the database. We can also drop the multiple tables in a single query.

Drop Table Syntax

DROP TABLE [IF EXISTS]  
   { database_name.schema_name.table_name }
   [ ,...n ]  ;

The syntax starts with DROP TABLE.

The optional IF EXISTS clause instructs the SQL Server to check if the table exists before deleting. The option IF EXISTS is only available from SQL Server 2016 onwards.

Specify the name of the table that you want to delete. You can specify the fully qualified table name in the format database_name.schema_name.table_name. If you only specify the table name, then SQL Server user uses the the current database & default schema (dbo unless changed).

You can specify more than one table just by separating the each table by a comma.

Drop Table Example

Create a new database and create a table Employee

CREATE TABLE Employee (
  EmployeeID  int            NOT NULL PRIMARY KEY,
  FirstName   varchar(50)    NOT NULL,
  LastName    varchar(50)    NOT NULL,

)

Now, You can delete the table using the following drop table query.

Drop Table Employee

Drop Table if Exists

Now, run a query to delete a table department, which does not exists in the database.

Drop Table Department

The SQL Server will raise the following error.

Cannot drop the table ‘Department’, because it does not exist or you do not have permission.

This is where we use the IF EXISTS clause. Now run the following SQL query. This clause is available since SQL Server 2016 onwards.

Drop Table IF EXISTS Department

Now SQL server does not return any errors. The table Department if exists will be deleted by SQL Server.

In older version of SQL Server, you can use the following query to delete a table only if it exists.

IF EXISTS 
        (SELECT 
             TABLE_NAME 
         FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Department') 
DROP TABLE Department

Drop Multiple Tables

You can delete the multiple tables in a single SQL query by separating each table with a comma.

Create following tables in the current database.

CREATE TABLE Table1 (
  Table1ID  int            NOT NULL PRIMARY KEY,
  col1      varchar(50)    NOT NULL,
 
)

CREATE TABLE Table2 (
  Table1ID  int            NOT NULL PRIMARY KEY,
  col1      varchar(50)    NOT NULL,
 
)

You can delete both the tables using a single query

drop table Table1, Table2

Drop Table in another database

You can drop a table, which exists in another database and schema by using the fully qualified table name.

In the following example, We create a new database HR and create the table Employee.

Create database HR          
Use HR

CREATE TABLE Employee (
  EmployeeID  int            NOT NULL PRIMARY KEY,
  FirstName   varchar(50)    NOT NULL,
  LastName    varchar(50)    NOT NULL,
 
)

Next, let us create another database Sales. And make it current by using the Use Sales

Create database Sales
Use Sales

Finally, wou can use the fully qualified table name to delete the table from the HR database

// This will throw an error as there is no employee table in current database
Drop Table Employee   

//Deletes the table from HR database
Drop Table HR.dbo.Employee

Drop Table using GUI Tool

We can also drop the table using the Table Designer

1. Open SSMS and connect to the database instance.
2. Select the database (HR) from the Databases node
3. Under Tables select the table, which you wish to delete
4. Right click and click on Delete option.

Important Notes on Drop Table

Dropping of a Table is a permanent operation. The table cannot be recovered. Only way to get it back is restoring it from a backup.

DROP TABLE will not delete the table, if it is referenced by a FOREIGN KEY constraint. You need to delete the FOREIGN KEY constraint or drop the referencing table first before dropping the table.

When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of the table.

The SQL Server drops the table even if a view or stored procedure references it. Those views or stored procedures will throw an error if we attempt to use them. Hence, you need to update or drop the referencing views and stored procedures.

Reference

Drop Table TSQL Reference