Understanding DROP TABLE Statement in SQL Server

The SQL Server DROP TABLE statement serves to drop the table from the database. This article will explain some use scenarios and illustrate the work of the DROP TABLE statement by examples.

For demonstration, we are using the wideworldimportors demo database – you can download it from here.

The syntax of the DROP TABLE statement is as follows:

Drop table [Database_name].[Schema_name].[table_name]

• Database_name: the name of the database from which you want to delete the table in SQL.
• Schema_Name: the name of the schema for which the table exists. If you have created the table in the DBO schema, we can skip this parameter. If the table is created in a non-default schema, we must specify the name of the schema name.
• Table_Name: the name of the table in SQL or MySQL you want to delete.

When we drop a table, the SQL Server performs the following actions:

1. Drops the table with data.
2. Drops the statistics of the table.
3. Drops indexes, constraints, and primary key associated with that table. If we have the foreign key relationships, we must drop the child table.

Before dropping the table, we must take care of the following things.

1. Ensure that the stored procedures, triggers, and views that depend on that table get altered or modified. You can find the necessary database objects using the sp_depends stored procedure.
2. Always use [database_name].[schema_name].[table_name] format to drop the correct table.
3. Remember that it is impossible to recover the specific table from the SQL Server backup. If you want to recover a specific table, you must buy third-party tools. If you think that the table might require references or custom reporting, make sure you generate a backup of the table by creating another copy. 

Demo Setup

In the demo Wideworldimportors database, there are tables named tblBusinessEntity, tblCountryRegion, tblCity, and tblCustomer.

• The tables tblBusinessEntity and tblCustomer are in the default schema, while tblCountryRegion and tblCity are in the Country schema.
• The tblCity is a system-versioned temporal table.
• The foreign key constraint is between tblBusinessEntity and tblCustomer tables. The BusinessEntityID column of tblCustomer references to BusinessEntityID column (Primary Key) of the tblBusinessEntity table.

I have inserted data from the AdventureWorks2017 database with the help of the INSERT INTO SELECT * FROM statement.

The T-SQL scripts to prepare the demo setup are as follows:

Use WideworldImportors
go
CREATE SCHEMA [country]
Go

Create the tables:

Use WideworldImportors
go
CREATE TABLE [tblBusinessEntity](
	[BusinessEntityID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_BusinessEntity_BusinessEntityID] PRIMARY KEY CLUSTERED 
([BusinessEntityID] ASC))
GO

CREATE TABLE [Country].[tblCountryRegion](
	[CountryRegionCode] [nvarchar](3) NOT NULL,
	[Name] varchar(500) NOT NULL
 CONSTRAINT [Country].[PK_CountryRegion_CountryRegionCode] PRIMARY KEY CLUSTERED 
([CountryRegionCode] ASC))
GO
CREATE TABLE [tblCustomer](
	[BusinessEntityID] [int] NOT NULL,
	[PersonType] [nchar](2) NOT NULL,
	[NameStyle] varchar(15) NOT NULL,
	[Title] [nvarchar](8) NULL,
	[FirstName] varchar(150) NOT NULL,
	[MiddleName] varchar(150) NULL,
	[LastName] varchar(150) NOT NULL,
	[Suffix] [nvarchar](10) NULL,
	[EmailPromotion] [int] NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED 
([BusinessEntityID] ASC))
GO
CREATE TABLE [Country].[tblCity](
	[CityID] [int] NOT NULL,
	[CityName] [nvarchar](50) NOT NULL,
	[StateProvinceID] [int] NOT NULL,
	[LatestRecordedPopulation] [bigint] NULL,
	[LastEditedBy] [int] NOT NULL,
	[ValidFrom] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
	[ValidTo] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_Country_Cities] PRIMARY KEY CLUSTERED 
(
	[CityID] ASC
),
	PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])
) ON [USERDATA] TEXTIMAGE_ON [USERDATA]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [Country].[City_Archive] )
)
GO

Create the foreign key index:

Use WideworldImportors
go
ALTER TABLE [tblCustomer]  WITH CHECK ADD  CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])
REFERENCES [tblBusinessEntity] ([BusinessEntityID])
GO

Insert the data into the tables:

Use WideworldImportors
Go
set identity_insert tblBusinessEntity on
Go
insert into tblBusinessEntity ([BusinessEntityID],[ModifiedDate])
select [BusinessEntityID],[ModifiedDate] from [AdventureWorks2017].[Person].[BusinessEntity]
Go
set identity_insert tblBusinessEntity off
Go
insert into tblCustomer ([BusinessEntityID],[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion],[ModifiedDate])
select [BusinessEntityID],[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName],[Suffix],[EmailPromotion],[ModifiedDate] 
from AdventureWorks2017.Person.Person
Go
insert into [Country].[tblCountryRegion]([CountryRegionCode],[Name]) 
select  [CountryRegionCode],[Name] from [AdventureWorks2017].[Person].[CountryRegion]
Go

Once the scripts are executed, we can verify whether the objects have been created correctly and if data has been inserted into tables.

Check the tables:

Use WideWorldImporters
Go
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('tblCustomer','tblCountryRegion','tblBusinessEntity')
Go

Output:

Get the row count in tables:

Use WideWorldImporters
Go
select object_name(object_id) as [Table Name], rows [Total Rows] from sys.partitions 
where object_id in (object_id('tblCustomer'),object_id('country.tblCountryRegion'),object_id('tblBusinessEntity'))
Go

Output:

Now, let us explore the usage of the DROP TABLE by examples.

Example 1: Simple application of the DROP TABLE statement

Sometimes while working with a table a mistake might occur. It happens when a user tries to DROP a Table that doesn’t exist. There is a simple way to avoid this – using the SQL DROP TABLE IF EXISTS command.

The syntax is as follows:

-- use database
USE [MyDatabase];
GO

-- attempt to run DROP TABLE only if it exists 
DROP TABLE IF EXISTS [dbo].[MyTable0];
GO

There is no need to check manually if the table you want to drop already exists because the command DROP TABLE IF EXITS in SQL will do it for you.

You might use SQL Server Management Studio (SSMS) if you need to DROP all tables.

Suppose we want to drop the tblCountryRegion table from our database. For that, run the following query:

use WideWorldImporters
Go
drop table tblCountryRegion

We receive the error:

Msg 3701, Level 11, State 5, Line 3
Cannot drop the table 'tblCountryRegion' because it does not exist 
or you do not have permission.

As mentioned earlier, to drop the table created in the non-default schema, we must write the DROP TABLE statement in the [schema_name].[table_name] format.

Run the following query:

use WideWorldImporters
Go
drop table [country].[tblCountryRegion]

Output:

Commands completed successfully.

We have dropped the table successfully.

Example 2: Drop a table that has a foreign key

Let us drop the tblBusinessEntity from the database – run the following query:

use WideWorldImporters
Go
Drop table tblBusinessEntity

It returns the error:

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

When we drop a table with a foreign key that references the primary key of the parent table, we must drop the parent table, and then we can drop the child table.

In our case, to drop the tblBusinessEntity table, we must drop the tblCustomer table. Run the following query:

use WideWorldImporters
Go
Drop table tblcustomer

Thus, the table has been dropped successfully.

After dropping tblcustomer, run the following statement to drop the tblBusinessEntity table:

use WideWorldImporters
Go
Drop table tblBusinessEntity

Output:

The table has been dropped successfully.

Example 3: Drop a Temporal table

The system-versioned temporal tables first appeared in SQL Server 2016. These tables can retrieve deleted and updated data, as the history table tracks changes that occurred in the temporal table. Refer to Getting Started with System-Versioned Temporal Tables for more information about the concept of temporal tables.

Dropping temporal tables is different from the process of dropping a usual table. We must perform the following steps:

1. Turn off the SYSTEM_VERSIONING.
2. Drop the temporal table.
3. Drop the history table.

Let’s examine the process – drop the [Country].[City] table.

Step 1: Run the following script to turn off the SYSTEM_VERSIONING:

Alter table [Country].[tblCity] set ( SYSTEM_VERSIONING = OFF  )

Step 2: Drop the [Country].[City] table by running the following DROP TABLE statement:

drop table [Country].[tblCity]

Step 3: Drop the [Country].[City_Archive] table by running the following query:

drop table [Country].[City_Archive]

Step 4: Verify that the table has been dropped by executing the following query:

Use WideWorldImporters
Go
select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('tblCity')

Output:

As you can see, the table has been dropped.

Summary

The DROP TABLE statement is necessary when we need to remove certain tables from the database for good. However, the usage of this statement has its peculiarities. We’ve explored several standard use cases and possible problems taking place during the tasks.

We also learned how to apply the DROP TABLE statement to drop a single table, a table with a foreign key, and the system-versioned temporal table. Hope that this article is helpful.

Read also

SQL DROP TABLE Statement and Various Use Cases

Tags: sql drop table, sql server Last modified: September 24, 2021