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:
- Drops the table with data.
- Drops the statistics of the table.
- 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.
- 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.
- Always use [database_name].[schema_name].[table_name] format to drop the correct table.
- 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.
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
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
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]
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
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:
- Turn off the SYSTEM_VERSIONING.
- Drop the temporal table.
- 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')
As you can see, the table has been dropped.
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.