Drop Table SQL Server Examples with T-SQL and SSMS

By: Joe Gavin | Updated: 2023-10-18 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > TSQL

Problem

Sometimes Microsoft SQL Server tables are created that are no longer needed. To help keep the SQL relational database clean there is a need to delete these unneeded tables. In this tutorial we look at how to delete a table using the DROP TABLE SQL command.

Solution

We'll look at the syntax of the DROP TABLE statement along with examples and some gotchas to look out for when deleting SQL Server tables. Keep in mind that DROP TABLE permanently removes one or more tables in a SQL Server database, so use caution because accessing the dropped table is often times not possible.

DROP TABLE SQL Server T-SQL Syntax

DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ] [ ; ]

Permissions Required for DROP TABLE Statement

DROP TABLE requires the user to have one of the following:

ALTER permission on the table's schema
CONTROL permission on the table
Membership in the db_ddladmin fixed database role

Example Data

First, we'll create a database called MyDatabase, create six simple tables in it called MyTable1 through MyTable6 with a basic table definition then insert a value in each. Here are the basic DDL and DML SQL statements:

CREATE DATABASE [MyDatabase];
GO
USE [MyDatabase];
GO
 
CREATE TABLE [dbo].[MyTable1] ( [Col1] [int] NULL );
GO
CREATE TABLE [dbo].[MyTable2] ( [Col1] [int] NULL );
GO
CREATE TABLE [dbo].[MyTable3] ( [Col1] [int] NULL );
GO
CREATE TABLE [dbo].[MyTable4] ( [Col1] [int] NULL );
GO
CREATE TABLE [dbo].[MyTable5] ( [Col1] [int] NULL );
GO
CREATE TABLE [dbo].[MyTable6] ( [Col1] [int] NULL );
GO
 
INSERT INTO [dbo].[MyTable1] (Col1) VALUES (1);
GO
INSERT INTO [dbo].[MyTable2] (Col1) VALUES (1);
GO
INSERT INTO [dbo].[MyTable3] (Col1) VALUES (1);
GO
INSERT INTO [dbo].[MyTable4] (Col1) VALUES (1);
GO
INSERT INTO [dbo].[MyTable5] (Col1) VALUES (1);
GO
INSERT INTO [dbo].[MyTable6] (Col1) VALUES (1);
GO

These SQL queries show the tables and data in each of them.

SELECT [Col1] FROM [dbo].[MyTable1];
SELECT [Col1] FROM [dbo].[MyTable2];
SELECT [Col1] FROM [dbo].[MyTable3];
SELECT [Col1] FROM [dbo].[MyTable4];
SELECT [Col1] FROM [dbo].[MyTable5];
SELECT [Col1] FROM [dbo].[MyTable6];

Drop a table with SQL Server Management Studio (SSMS) Object Explorer

Expand Server dropdown
Expand Database dropdown
Right click on table to drop
Delete

Right click on Tables
Refresh

And [dbo].[MyTable1] table is gone.

Drop a Table in One Database from Another Database

To drop a table from another database (applies to on-premises not Azure SQL Database) we simply give it the database and names in the form [database_name].[schema_name].[table_name].

USE master
GO
 
DROP TABLE [MyDatabase].[dbo].[MyTable2];
GO

And if we try to select from the table we get an error that shows us the table is no longer there.

SELECT [Col1] FROM [MyDatabase].[dbo].[MyTable2];

Drop Table Statement Within a Database

To drop a table from within the current database (on-premises or Azure SQL Database) we simply pass the name of the table in the form [schema_name].[table_name].

USE [MyDatabase]
GO
 
DROP TABLE [dbo].[MyTable3];
GO

As before, we get an error that shows us the table is no longer there trying when we try to select from it.

SELECT [Col1] FROM [dbo].[MyTable3];

SQL Drop Table Syntax Within a Database if Table is Owned by the dbo Schema

Passing the schema to the DROP TABLE statement is optional if the table is owned by the dbo schema.

USE [MyDatabase]
GO
 
DROP TABLE [MyTable3];
GO

As expected, the table is no longer there.

SELECT [Col1] FROM [dbo].[MyTable4];
GO

Drop More Than One Table with a Single DROP TABLE Statement

Separate the table names with a comma to drop more than table with a single Transact-SQL DROP TABLE statement.

USE [MyDatabase]
GO
 
DROP TABLE [dbo].[MyTable5],[dbo].[MyTable6];
GO

Both tables are gone.

SELECT [Col1] FROM [dbo].[MyTable5];
GO
SELECT [Col1] FROM [dbo].[MyTable6];
GO

Dropping Tables That Has a Foreign Key Relationship

Let's create a couple of simple tables with Transact-SQL called Parent and Child with the Child table having a Foreign Key in the Parent table then populate them with one row each.

CREATE TABLE [dbo].[Parent] 
(
   ID INT PRIMARY KEY,
   Name VARCHAR(10) NULL
);
GO
INSERT INTO [dbo].[Parent]([ID],[NAME]) VALUES (1,'Joe');
GO
 
CREATE TABLE [dbo].[Child] 
(
   CID INT PRIMARY KEY,
   ID INT
);
GO

ALTER TABLE [dbo].[Child]
ADD CONSTRAINT FK_Parent_Child FOREIGN KEY (CID) 
REFERENCES [Parent](ID)
GO
INSERT INTO [dbo].[Child] (CID,ID) VALUES (1,1);
GO
 
SELECT * FROM [dbo].[Parent];
GO
SELECT * FROM [dbo].[Child] ;
GO

We'll see the Parent table can't be dropped where there is a relationship to the Child table which is the referencing table.

DROP TABLE [dbo].[Parent];
GO

We get the error 'Msg 3726, Level 16, State 1, Line 156 Could not drop object 'dbo.Parent' because it is referenced by a FOREIGN KEY constraint.'.

But we're successful if we drop the Child table first, then the Parent table.

DROP TABLE [dbo].[Child];
GO
 
DROP TABLE [dbo].[Parent];
GO

And to drop the Parent table without dropping the Child Table, we can drop the Primary Key Constraint first then the table.

ALTER TABLE [dbo].[Child]
DROP CONSTRAINT FK_ID;
GO
 
DROP TABLE [dbo].[Parent];
GO

Archive table before deleting

We may want to archive an existing table prior to dropping it. This can be done using SELECT INTO which will create a table that's the same as the source table and then populate it with the source table's values, effectively making a copy.

To demonstrate, we'll create a new table called MyTable7 and insert a few records into it.

CREATE TABLE [dbo].[MyTable7]
(
    emp_num INT IDENTITY(1,1),  
    name varchar (20) NOT NULL  
);
GO
INSERT INTO [dbo].[MyTable7] (name) VALUES ('a');
INSERT INTO [dbo].[MyTable7] (name) VALUES ('b');
INSERT INTO [dbo].[MyTable7] (name) VALUES ('c');
GO

SELECT * 
INTO [dbo].[MyTable7_archived] 
FROM [dbo].[MyTable7];
GO

We now have two tables with the same data and we can then drop the original table, if needed. This way we have a temporary table of sorts to store the data, if necessary

SELECT * FROM [dbo].[MyTable7]
SELECT * FROM [dbo].[MyTable7_archived]

Rename SQL Server Table

Another scenario is if we need to archive a table before creating a new table with the same name. We can do this with the built-in stored procedure, sp_rename.

Create a table called MyTable and insert some values into it.

CREATE TABLE [dbo].[MyTable8]
(
    emp_num INT IDENTITY(1,1),  
    name varchar (20) NOT NULL  
);
GO
 
INSERT INTO [dbo].[MyTable8] (name) VALUES ('d');
INSERT INTO [dbo].[MyTable8] (name) VALUES ('e');
INSERT INTO [dbo].[MyTable8] (name) VALUES ('f');
GO

Now, we execute sp_rename in the form 'EXEC sp_rename 'old_name', 'new_name'. Square brackets, if any, need to be removed and we omit the schema name from 'new_name'. Otherwise, the brackets and / or schema become part of the new name. The SQL Server generates this friendly warning: 'Caution: Changing any part of an object name could break scripts and stored procedures.'

-- remove brackets, enclose each table in single quotes, and omit schema from new name
EXEC sp_rename 'dbo.MyTable8', 'MyTable8_renamed'

Attempting to select from [dbo].[MyTable8] fails saying the object is invalid.

SELECT * FROM [dbo].[MyTable8];

But here we see selecting from [dbo].[MyTable8_renamed] is successful.

SELECT * FROM [dbo].[MyTable8_renamed];

Alternatively, to rename a table in the SSMS GUI, we'll create and populate one more table, called MyTable9.

CREATE TABLE [dbo].[MyTable9]
(
    emp_num INT IDENTITY(1,1),  
    name varchar (20) NOT NULL  
);
GO
 
INSERT INTO [dbo].[MyTable9] (name) VALUES ('g');
INSERT INTO [dbo].[MyTable9] (name) VALUES ('h');
INSERT INTO [dbo].[MyTable9] (name) VALUES ('i');
GO

In the SSMS Objects Explorer

Right click on the table name
Rename

Enter new table name
Click outside the text box

And the table is renamed.

Next Steps

Here are links with some further information:

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-10-18

Drop Table SQL Server Examples with T-SQL and SQL Server Management Studio