This article gives an overview of the SQL DROP TABLE statement to remove one or more tables from a database.
In my earlier article, Difference between SQL Truncate and SQL Delete statements in SQL Server, we explored to delete data from an existing data. We might delete whole data using both SQL Delete and SQL Truncate statements. We might also delete specific data from the SQL Server tables using SQL Delete statement. SQL Delete and Truncate do not move the object structure from the database.
Sometimes, we do want to perform database clean up by removing unnecessary tables. Let’s say you want to make bulk changes to a table. Most of the DBA’s take table-level backup before making any change to it. It involves creating another backup table in a similar database with a different name.
In the following table, we want to delete multiple records.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT [BusinessEntityID] ,[NationalIDNumber] ,[LoginID] ,[OrganizationNode] ,[OrganizationLevel] ,[JobTitle] ,[BirthDate] ,[MaritalStatus] ,[Gender] ,[HireDate] ,[SalariedFlag] ,[VacationHours] ,[SickLeaveHours] ,[CurrentFlag] ,[rowguid] ,[ModifiedDate] FROM [AdventureWorks2017].[HumanResources].[Employee] |
Before removing the data, take a backup using SELECT INTO command.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT [BusinessEntityID], [NationalIDNumber], [LoginID], [OrganizationNode], [OrganizationLevel], [JobTitle], [BirthDate], [MaritalStatus], [Gender], [HireDate], [SalariedFlag], [VacationHours], [SickLeaveHours], [CurrentFlag], [rowguid], [ModifiedDate] INTO [AdventureWorks2017].[HumanResources].[Employee13072019] FROM [AdventureWorks2017].[HumanResources].[Employee]; |
It created another table with existing column structure and copies the data into it. We need to perform regular clean-up of these backup tables. It takes unnecessary disk space and if you do index maintenance for all indexes, it might add extra overhead to the system.
Let’s explore the SQL DROP TABLE in the next action.
Overview of SQL DROP TABLE
We use the SQL DROP Table command to drop a table from the database. It completely removes the table structure and associated indexes, statistics, permissions, triggers and constraints. You might have SQL Views and Stored procedures referencing to the SQL table. SQL Server does not remove these stored procedures and views. We need to drop them explicitly. We should check object dependencies before removing a SQL table.
The syntax for SQL DROP TABLE
|
1 |
DROP TABLE [database_name].[schema_name].[table_name] |
It uses the following parameters.
- Database_name: Specify the database name in which table exists. We can skip this parameter if we execute the drop command in the current database context
- Schema_name: Specify the schema name for which the object exists. If the object belongs to the default schema DBO, we can skip this parameter. SQL Server automatically uses dbo schema. We must specify the schema name if the object belongs other than the default schema
- Table name: Specify the table that we want to remove
Example 1: Drop a single table using the SQL DROP Table statement
Execute the following query to drop HumanResources.Employee13072019 table from the AdventureWorks2017 database.
|
1 |
Drop table Employee13072019 |
It gives the following error message. We get this error because the object belongs to the default schema dbo.
To fix this, we need to specify schema name along with the table name.
|
1 |
Drop table AdventureWorks2017.HumanResources.Employee13072019 |
Alternatively, we can use the following query to drop a SQL table.
|
1 2 3 |
Use AdventureWorks2017 Go Drop table HumanResources.Employee13072019 |
Example 2: Drop multiple tables together using the SQL DROP Table statement
We can drop multiple tables together using a single DROP Table statement as well.
Let’s create three tables and later we will drop it.
|
1 2 3 |
CREATE TABLE Sam(id INT); CREATE TABLE Amp(id INT); CREATE TABLE Rmp(id INT); |
Now, we can use the following drop table statement and specify all table names together to drop it.
|
1 |
DROP TABLE Sam, Amp, Rmp; |
Example 2: Drop a SQL table having a foreign key constraint using the SQL DROP Table statement
In SQL Server, we can use a foreign key between multiple table columns to link data between these tables. We cannot drop the table directly in this case.
Let’s understand this using an example. We will create two SQL tables Department and Employee. In the database diagram, you can see that we have a foreign key constraint for on the Dept_id column
Execute the following script to create both tables.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE Department (Dept_id INT IDENTITY PRIMARY KEY, Dept_name VARCHAR(50) NOT NULL ); CREATE TABLE Employee1 (EmpID INT IDENTITY PRIMARY KEY, EmpName VARCHAR(50) NOT NULL, Dept_id INT NOT NULL, FOREIGN KEY(Dept_id) REFERENCES department(dept_id) ); |
Let’s try to drop departments table using SQL DROP TABLE statement.
|
1 |
Drop table Department |
You get the following error message.
Foreign key relationships are like a parent-child relationship. We cannot delete a parent table that is referenced by a foreign key constraint. We need to either remove the foreign key relationship or drop the child table first.
In my example, we need to drop the Employee1 table first because it has a foreign key relationship with the department table.
|
1 2 |
Drop table Employee1 DROP TABLE Department; |
We can use a single SQL Drop Table statement as well in such case with a caution to drop the referencing table first.
|
1 |
DROP TABLE Employee1, Department; |
Example 3: Drop a temp table using the SQL DROP Table statement
We can also drop a temp table in a way similar to the regular table. The following example creates a temp table and drops it.
|
1 2 3 4 5 6 |
CREATE TABLE #temp1(col1 INT); GO INSERT INTO #temp1 VALUES(100); GO DROP TABLE #temp1; |
Example 4: Dropping a table using IF EXISTS
Usually, developers check for the existence of any database object in the database and drop the object if it exists.
If we try to drop a table that does not exist, we get the following error message.
We do not want any error in executing queries, especially during the execution of a bunch of code. Before SQL Server 2016, developers use the IF EXISTS statement and check for the object existence before dropping it.
For example, in the following query, we check the department table in the sys .objects.
If the object exists, execute the drop table statement else, no actions required.
|
1 2 3 4 5 6 7 8 9 |
IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'Department') AND type IN(N'U') ) DROP TABLE Department; GO |
Alternatively, we can check the object id of the SQL table and execute the drop table statement if it is not NULL.
|
1 2 3 |
IF OBJECT_ID('Department', 'U') IS NOT NULL DROP TABLE Department; GO |
These approaches work fine. However, the issue is that you need to write a long transact SQL code. Starting from SQL Server 2016, we can use the new syntax of SQL DROP Table. It drops a SQL table if it already exists.
|
1 |
DROP Table IF EXISTS Table_name |
It is a short version of the code we executed before. Let’s try to drop the department table using this new code. It is a small and easy way to drop a table.
|
1 |
DROP TABLE IF EXISTS Department; |
Example 5: Dropping a table having a reference in the stored procedures, views
SQL Server does not give any error message if you drop a SQL table that is being used in the stored procedure, views. We can use the SCHEMABINDING option, but it is not in the scope of this article.
We can check the dependencies of an object using SSMS. Right-click on a table and click on View Dependencies. It opens a separate window and displays the dependencies.
You can look at the dependencies and resolve it so that the procedures and views can function correctly after dropping this object.
Conclusion
In this article, we explored the SQL DROP Table statement for removing the objects from the SQL database. You should be careful before dropping any object in the production database.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023