The Tutorial Guide to SQL Server DROP TABLE Statement

The SQL DROP statement is a command that deletes existing database components or the entire databases with all their data permanently. There are other SQL commands with a similar effect (TRUNCATE or DELETE), but the specificity of the DROP command is that it deletes everything at once. For instance, DROP TABLE removes the table data, indexes, triggers, permissions, constraints – the entire schema of the table.

The DROP statement requires one to be extremely careful when using it. Once performed, it can’t be rolled back. The information is lost for good. The only chance to return the data will be to restore a backup. Still, in many cases, we have to apply DROP statements. This article will focus on such cases, the specific DROP variants, and how to use them safely.

Table of contents

• Preparations
• SQL Drop Table Statement
• Example 1: Drop a Physical Table
• Example 2: Delete Temp Table
• SQL Drop Index Examples
• Example 1: Drop One or Multiple Indexes from the Table
• Example 2: Drop Index with MAXDOP and ONLINE Option
• How to Drop Database in SQL Server
• Example: Drop the Database which is Being Used
• Summary

Preparations

To examine our cases, we’ll need a database with the data. I have created a test database named EltechEmployees with the following query:

USE [master] 
go 
CREATE DATABASE [EltechEmployees] 
go

In that database, I have created a table tblEmployees – the following query serves for that:

USE [EltechEmployees] 
go 
CREATE TABLE [tblEmployees] 
  ( 
     [businessentityid] [INT] NOT NULL, 
     [nationalidnumber] [NVARCHAR](15) NOT NULL, 
     [loginid]          [NVARCHAR](256) NOT NULL, 
     [jobtitle]         [NVARCHAR](50) NOT NULL, 
     [birthdate]        [DATE] NOT NULL, 
     [maritalstatus]    [NCHAR](1) NOT NULL, 
     [gender]           [NCHAR](1) NOT NULL, 
     [hiredate]         [DATE] NOT NULL, 
     [vacationhours]    [SMALLINT] NOT NULL, 
     [sickleavehours]   [SMALLINT] NOT NULL, 
     [modifieddate]     [DATETIME] NOT NULL 
     
  ) 
go 

The next step is creating two non-clustered indexes and one clustered index on the tblEmployees table.

• Non-clustered indexes: IDX_tblEmployees_loginID and IDX_tblEmployees_nationalidnumber
• One clustered index: IDX _tblEmployees_gender

To create these indexes, use the following query:

USE [EltechEmployees] 
go 
CREATE INDEX [IDX_tblEmployees_loginID]   ON [tblEmployees](loginid) 
go 
CREATE INDEX [IDX_tblEmployees_nationalidnumber]  ON [tblEmployees](nationalidnumber) 
go 
CREATE Clustered INDEX [IDX_tblEmployees_gender]  ON [tblEmployees](gender) 
go

Let’s view the indexes we created:

SELECT Object_name([Index].object_id)                               [Table Name] 
       , 
       [Index].NAME 
       [Index Name] 
       , 
       Col_name([Index Column].object_id, [Index Column].column_id) 
       [Index Column_Name], 
       [Index Column].index_column_id, 
       CASE 
         WHEN is_primary_key = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Primary Key], 
       CASE 
         WHEN is_unique = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Unique Key] 
FROM   sys.indexes AS [Index] 
       INNER JOIN sys.index_columns AS [Index Column] 
               ON [Index].object_id = [Index Column].object_id 
                  AND [Index].index_id = [Index Column].index_id 
WHERE  [Index].is_hypothetical = 0 
       AND [Index].object_id = Object_id('tblEmployees');

Output

Now we can proceed to the practical examples.

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

Drop table [EltechEmployees].[dbo].[tblEmployees]

CREATE TABLE [#tblemployees] 
  ( 
     [businessentityid] [INT] NOT NULL, 
     [nationalidnumber] [NVARCHAR](15) NOT NULL, 
     [loginid]          [NVARCHAR](256) NOT NULL, 
     [jobtitle]         [NVARCHAR](50) NOT NULL, 
     [birthdate]        [DATE] NOT NULL, 
     [maritalstatus]    [NCHAR](1) NOT NULL, 
     [gender]           [NCHAR](1) NOT NULL, 
     [hiredate]         [DATE] NOT NULL, 
     [vacationhours]    [SMALLINT] NOT NULL, 
     [sickleavehours]   [SMALLINT] NOT NULL, 
     [modifieddate]     [DATETIME] NOT NULL 
  ) 
go

IF Object_id(N'tempdb..#tblEmployees', N'U') IS NOT NULL 
  DROP TABLE #tblemployees; 
go 

SQL Drop Index Examples

Our first case is dropping indexes from a table.

Example 1: Drop One or Multiple Indexes from the Table

The syntax to drop multiple indexes is following:

Drop index 
[IndexName] on [SchemaName].[TableName] ,
[IndexName] on [SchemaName].[TableName] 

• IndexName: Specify the index name you want to drop after the Drop Index statement.
• [SchemaName].[TableName]: Specify the database name, schema name, and table name. The Drop table statement allows you to use the two-part name of any database object. The values of [SchemaName].[TableName] must be specified after the ON keyword.

Suppose we want to drop IX_loginID_tblEmployees and IDX_nationalidnumber_tblEmployees on the tblEmployees table. To do that, run the below query:

DROP INDEX 
[IDX_tblEmployees_loginID] ON [dbo].[tblEmployees], 
[IDX_tblEmployees_nationalidnumber] ON [dbo].[tblEmployees] 

Once indexes are dropped, run the following query to view them:

SELECT Object_name([Index].object_id)                               [Table Name] 
       , 
       [Index].NAME 
       [Index Name] 
       , 
       Col_name([Index Column].object_id, [Index Column].column_id) 
       [Index Column_Name], 
       [Index Column].index_column_id, 
       CASE 
         WHEN is_primary_key = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Primary Key], 
       CASE 
         WHEN is_unique = 1 THEN 'Yes' 
         ELSE 'No' 
       END 
       [Is Unique Key] 
FROM   sys.indexes AS [Index] 
       INNER JOIN sys.index_columns AS [Index Column] 
               ON [Index].object_id = [Index Column].object_id 
                  AND [Index].index_id = [Index Column].index_id 
WHERE  [Index].is_hypothetical = 0 
       AND [Index].object_id = Object_id('tblEmployees'); 

Output

Example 2: Drop Index with MAXDOP and ONLINE Option

We can use MAXDOP and ONLINE options while dropping the index.

Note: We can drop only clustered index using MAXDOP and ONLINE options.

The syntax is as follows:

DROP Index [indexName] on [schemaname].[tablename] with (MAXDOP = @maxdop_val, ONLINE = @online_option)

• Indexname: Specify the index name you want to drop.
• [SchemaName].[TableName]:  Specify the three-part name of the table.
• @MaxDop_val: Specify the value of the MAXDOP parameter.
• @online_option: The valid values are ON and OFF.

Suppose we want to drop the PK_Employee_BusinessEntityID  index with MAXDOP and ONLINE options.

Drop the index using the value of the MAXDOP option to 5, and the ONLINE option is ON. The query to drop the index is following:

DROP INDEX [IDX_tblEmployees_gender] ON [dbo].[tblEmployees] WITH (maxdop=5, 
online=ON)

All indexes have been dropped from the tblEmployees table.

How to Drop Database in SQL Server

Drop database statement works to drop the entire database. The syntax is following:

Drop database [DatabaseName]

[DatabaseName]: Specify the name of the database name you want to drop.

Permissions

To drop the database, you need the CONTROL or ALTER ANY DATABASE permission on the database. Or, you must be a member of the db_owner fixed database role.

Notes:

1. When we drop any database in the ONLINE state, the SQL Server will drop the database files from the disk. However, if you are dropping the database in the OFFLINE state, the SQL Server does not drop the database files. We must delete them manually.
2. To drop the database published for transactional or published/subscribed to the merge replication, we must destroy the replication and then drop the database.
3. If users are connected to the database, we cannot drop the database. First, we must change its state to SINGLE_USER.

Example: Drop the Database which is Being Used

We want to drop the EltechEmployees database. To do that, run the following query:

USE master 
go 
DROP DATABASE [EltechEmployees] 

If users are connected to the database, you will encounter the following error:

Msg 3702, Level 16, State 4, Line 3
Cannot drop database "EltechEmployees" because it is currently in use.

To rectify the error, we must execute the following query:

USE [master] 
go 
ALTER DATABASE eltechemployees SET single_user WITH ROLLBACK immediate 

The above query sets the database in the SINGLE_USER mode and drops all users connected to the database.

Now, let us run the DROP DATABASE statement:

USE master 
go 
DROP DATABASE [EltechEmployees] 

Output

As you can see, the command was executed successfully.

Summary

Thus, we’ve examined the DROP INDEX, DROP TABLE, and DROP DATABASE statements specific variants of the SQL DROP statement. Hope that the practical examples presented in this article helped explain how and when to apply these commands.