Rajendra Gupta
View Dependencies

SQL DROP TABLE statement overview

July 16, 2019 by

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.

Before removing the data, take a backup using SELECT INTO command.

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

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.

It gives the following error message. We get this error because the object belongs to the default schema dbo.

: Drop a single table using the SQL DROP Table statement

To fix this, we need to specify schema name along with the table name.

Alternatively, we can use the following query to drop a SQL table.

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.

Now, we can use the following drop table statement and specify all table names together to drop it.

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

database diagram

Execute the following script to create both tables.

Let’s try to drop departments table using SQL DROP TABLE statement.

You get the following error message.

error while dropping a table

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.

We can use a single SQL Drop Table statement as well in such case with a caution to drop the referencing table first.

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.

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.

Dropping a table using IF EXISTS

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.

Alternatively, we can check the object id of the SQL table and execute the drop table statement if it is not NULL.

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.

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.

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.

View 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.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

835 Views