SQL Server DROP TABLE

Learning Objective

The objective of this tutorial to teach you, how to remove one or more existing tables using SQL Server DROP TABLE statement.

Introduction to SQL Server DROP TABLE statement

While working with SQL Server on day to day basis, you may need to drop one or more tables which are no longer in use from the database.

The SQL Server DROP TABLE statement allows you to remove one or more existing table from the database.

SQL Server DROP TABLE Syntax

The basic syntax of the SQL Server DROP TABLE syntax is as follows:

DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name;

In this syntax,

  • DROP TABLE – keywords to drop an existing table from the database.
  • IF EXISTS – Optional. The feature introduced from the SQL Server 2016 13x inwards. By providing this this, you can ensure that to drop a table only if it exist. If you are not using IF EXISTS, and try to drop a non-existing table, SQL Server gives error.
  • database_name – Name of the database where the table belongs. This is optional. If you does not provide any database name in DROP TABLE statement, SQL Server tries to remove table from the current database where you are connected.
  • schema_name – Name of the schema where the table belongs.
  • table_name – Name of the table which you want to drop.

When you drop a table from SQL Server, it also delete all the data, constraints, triggers, permission associated with the tables. However it does not delete any view or stored procedure which has reference to that table. In order to delete drop these dependent objects, you have to execute DROP VIEW and DROP PROCEDURE statements explicitly.

SQL Server also allows you to drop multiple tables at a time. Below is the statement for this.

DROP TABLE [database_name.][schema_name.]table_name_1,
           [schema_name.]table_name_2, …
           [schema_name.]table_name_n;

SQL Server DROP TABLE Examples

To understand the concept of SQL Server DROP TABLE with clarity, let’s check few examples now.

1) Drop a table that does not exist

The following example remove a table named teacher from the university schema.

DROP TABLE IF EXISTS university.teacher;

The table named teacher does not exist in university schema. However, the above statement successfully executed without removing any table because we have mentioned IF EXISTS clause in the statement.

Output Message

Commands completed successfully.

2) DROP a single table

The following statement creates a new table in the university schema.

CREATE TABLE university.teacher
(
    id INT PRIMARY KEY IDENTITY,
    first_name nVARCHAR(50) NOT NULL,
    last_name nVARCHAR(50) NOT NULL,
    contact_no nVARCHAR(20) NOT NULL
);
SQL Server DROP TABLE

Now, if you want to remove the teacher table from the university schema, use the following statement.

DROP TABLE university.teacher;

3) DROP a table with FOREIGN KEY Constraint

The following statements create two tables named employee and department in company schema.

CREATE SCHEMA company;
GO

CREATE TABLE company.department
(
    dept_id INT PRIMARY KEY,
    dept_name nVARCHAR(100)   
);

CREATE TABLE company.employee
(
   emp_id INT PRIMARY KEY IDENTITY (1,1),
   first_name nVARCHAR(50) NOT NULL,
   last_name nVARCHAR(50) NOT NULL,
   dob DATE NOT NULL,
   join_date DATE NOT NULL,    
   phone nVARCHAR(20),
   dept_id INT NOT NULL,
   FOREIGN KEY (dept_id) REFERENCES company.department (dept_id)
);

Now let’s try to drop department table from the schema.

DROP TABLE company.department;

SQL Server will give the following error.

Msg 3726, Level 16, State 1, Line 35
Could not drop object 'company.department' because it is referenced by a FOREIGN KEY constraint.

SQL Server does not allow to drop a table that is referenced by the foreign key constraints. To drop this kind of table either you have to remove the foreign key constraint or drop the referencing table first. In our example, you have to either drop foreign key constraints from the table or drop company.employee table first before removing the company.teacher table. 

DROP TABLE company.employee;
DROP TABLE company.department;

You can also remove the above tables using a single DROP TABLE statement. But always remember that you need to mention the referencing table first in the list. In this case, compay.employee is the referencing table.

DROP TABLE company.employee, company.department;

Summary

In this tutorial you have learned how to remove one or more existing tables using SQL Server DROP TABLE statement.

Advertisement