SQL DROP TABLE Statement and Various Use Cases

The SQL DROP TABLE statement serves to drop the table from the database. It removes the table and its data and indexes associated with it. The statement is irreversible. Thus, you can restore the table only by restoring the backup.

This article covers the following cases:

1. Delete one or multiple tables.
2. Delete the table used in a View.
3. Delete the table having a foreign key.
4. Delete the table having clustered and non-clustered indexes.
5. Delete the table from the replication setup.

The syntax of the DROP TABLE statement is the following:

IF EXISTS DROP TABLE [DatabaseName].[SchemaName].[TableName]

Note: If you are using SQL Server 2016 and above, you can specify IF EXISTS before the DROP TABLE statement.

Also, you specify the table name after the DROP TABLE statement. The table name format should be [Database Name].[Schema Name].[Table Name].

A simple example of the DROP TABLE statement

For the demonstration, I have restored the AdventureWorks2017 database on my workstation. Here, I am going to delete the employees table.

Use the following query used to drop the table:

DROP TABLE [AdventureWorks2017].[dbo].[employees]

I have installed the Devart dbForge SQL Complete tools in SQL Server Management Studio. When I execute a SQL DROP TABLE statement, it prompts a confirmation dialog box, as shown below:

Click on Execute Anyway. The table is dropped.

To verify, run the following query:

SELECT * FROM dbo. employee

If it dropped the table successfully, you will receive the following error:

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.employee'.

Drop the table used in a View

Here, we will try to drop a table used in a database view. Execute the following script to create a view named vPerson:

CREATE VIEW vPerson 
AS
SELECT * FROM Person p

Run the DROP TABLE command to remove the Person table:

DROP TABLE [AdventureWorks2017].[dbo].[person]

Once the table is dropped, run the SELECT query to populate the data from vPerson. It will produce an error:

Msg 208, Level 16, State 1, Procedure vPerson, Line 3 [Batch Start Line 0]
Invalid object name 'Person'.
Msg 4413, Level 16, State 1, Line 1
Could not use the view or function 'vPerson' because of binding errors.

This error occurs when the object on which the view is dependent is dropped.

Drop the table having a foreign key

If you have created a foreign key on a table, you cannot drop the parent table before dropping the child table. It means that you must drop the child table or the foreign key that references the child table first.

There are two tables named tblstudent and tblSchool – the ER diagram is the following:

Note: I am using dbForge Studio for SQL Server 2019 to create a database diagram – it gives appropriate insight into the table structure with all its elements and entity-relationship between tables.

The following query creates the tables named tblstudent and tblSchool and their primary keys and foreign keys:

CREATE TABLE tblSchool
(
	SchoolID INT identity(1,1),
	SchoolName VARCHAR(500),
	City varchar(250),
	CONSTRAINT PK_SchoolID PRIMARY KEY  (SchoolID)  
)
GO
CREATE TABLE tblStudent
(
StudentID INT IDENTITY(1,1) ,
StudentName VARCHAR(250),
StudentGrade CHAR(1),
SchoolID INT,
CONSTRAINT PK_StudentID PRIMARY KEY  (StudentID),
Constraint FK_SchoolID FOREIGN KEY (SchoolID) REFERENCES tblSchool (SchoolID)
)
GO

Run the following query to drop the tblstudent table:

Drop table [tblSchool]

The query returns the following error:

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

In our demo, if you want to drop the tblschool table, you must drop the tblstudent table first. Have a look at the query to drop the tblStudent and tblschool table:

Drop table [tblSchool]
Go
Drop table [tblStudent]
Go

It is also possible to drop the batch table in a single DROP TABLE statement. Run the query as follows:

Drop table [tblSchool], [tblStudent]

Drop a table with indexes

Let us observe another scenario of the DROP TABLE statement behavior. Assume that we have created a table named tblstudent. It has a clustered and a non-clustered index.

When we drop a table using DROP TABLE, does it remove the indexes? Let us check it. I have created a table named tblStudent that has one clustered and one non-clustered index. The query is the following:

CREATE TABLE tblStudent
(
StudentID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StudentName VARCHAR(250),
StudentGrade CHAR(1),
SchoolID INT
)
GO
CREATE NONCLUSTERED INDEX IDX_tblStudent_StudentName ON tblStudent(StudentName) 
GO

We can see the list of the indexes by querying the sys.indexes DMV.

Once table is dropped, run the following query:

SELECT Object_name(object_id) AS [Table Name],
       NAME                   AS [Index Name],
       type_desc              [Index Type]
FROM   sys.indexes
WHERE  object_id = Object_id('tblStudent')  

Output:

As you can see, we have created both the clustered and non-clustered indexes for the tblStudent table.

Now, run the following command to delete the table:

Drop table tblStudent

Again, run the same query that we used to populate the list of indexes:

SELECT Object_name(object_id) AS [Table Name],
       NAME                   AS [Index Name],
       type_desc              [Index Type]
FROM   sys.indexes
WHERE  object_id = Object_id('tblStudent')  

Output:

The above screenshot illustrates that the query returned an empty result set. Thus, when we drop a table, it drops the indexes associated with that table too.

Drop a table from Replication

Suppose you want to drop the table that is part of replication. In this case, you must perform additional steps.

I have configured replication between the two instances of SQL Server 2019 named SQL01 and SQL02. Next, I have replicated the tblStudent and tblSchool tables.

We drop the tblSchool table from the SQL01 by executing the following query:

use codingsight
go
drop table tblSchool

Output:

Msg 3724, Level 16, State 3, Line 3
Cannot drop the table 'tblSchool' because it is being used for replication.

The above error is explanatory. To drop the table, we must first remove it from replication:

1. Open SQL Server Management Studio and Connect to SQL01.
2. Expand Replication -> Expand Local Publication.
3. Right-click on Publication and select Properties.

In the Publication Properties window, click on Articles.

There, you can see the list of tables. Uncheck the tblSchool table and click OK.

Note: When you drop the article from replication, the previous snapshots become invalid. You must generate a new snapshot after dropping the article.

Once the table is removed from the replication, execute the DROP TABLE command on SQL01:

use codingsight
go
drop table tblSchool

Output:

As you can see, the table has been dropped. 

Tags: sql complete, sql drop table, sql functions, sql server Last modified: September 17, 2021