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


By:   |   Updated: 2021-02-08   |   Comments (1)   |   Related: More > T-SQL


Problem

Sometimes there are Microsoft SQL Server tables that created that are no longer needed. To help keep the 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 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 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

A SELECT shows 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];
show table contents

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

  1. Expand Server dropdown
  2. Expand Database dropdown
  3. Right click on table to drop
  4. Delete
delete table
  1. OK
delete table
  1. Right click on Tables
  2. Refresh
refresh

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

table deleted

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
drop table in another database

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];
verify table is gone

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
drop table from within a database

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];
verify table is gone

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
drop table if owned by dbo

As expected, the table is no longer there.

SELECT [Col1] FROM [dbo].[MyTable4];
GO
verify table is gone

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
drop 2 tables

Both tables are gone.

SELECT [Col1] FROM [dbo].[MyTable5];
GO
SELECT [Col1] FROM [dbo].[MyTable6];
GO
verify tables are gone

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
create tables with key

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.'.

attempt to drop parent

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
drop child then parent

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
drop constraint

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
archive table
SELECT * 
INTO [dbo].[MyTable7_archived] 
FROM [dbo].[MyTable7];
GO
select into

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]
verify both tables

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
create table

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' 
rename table

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

SELECT * FROM [dbo].[MyTable8];
verify table is gone

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

SELECT * FROM [dbo].[MyTable8_renamed];
verify renamed table

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

  1. Right click on the table name
  2. Rename
rename table in SSMS
  1. Enter new table name
  2. Click outside the text box
rename table in SSMS

And the table is renamed.

renamed
Next Steps

Here are links with some further information:



Last Updated: 2021-02-08


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips



Comments For This Article




Wednesday, February 10, 2021 - 2:00:01 AM - Abdul Mueed Siddiqui Back To Top (88203)
Thank you very much, very good explanation.


download





Recommended Reading

SQL Server Cursor Example

Using MERGE in SQL Server to insert, update and delete at the same time

Rolling up multiple rows into a single row and column for SQL Server data

How to use @@ROWCOUNT in SQL Server

SQL Server Loop through Table Rows without Cursor














get free sql tips
agree to terms