Understanding DROP TABLE Statement in SQL Server

Total: 1 Average: 5

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.

CodingSight - Understanding DROP TABLE Statement in SQL Server

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.
  • 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 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 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:

The output of the query to verify whether the objects have been created correctly and if data has been inserted into tables

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:

The output of the query to get the row count in tables

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

Example 1: Simple application of the DROP TABLE statement

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.
Error Message - 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.
The output of the query to drop the table - the DROP TABLE statement in the [schema_name].[table_name] format

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 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.
Error Message - 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
The output of the query. To drop the tblBusinessEntity table, we must drop the tblCustomer table

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 output of the query to drop the tblBusinessEntity table

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:

The output of the query to drop a Temporal table

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

Nisarg Upadhyay

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.