SQL Drop Column Examples

By: Joe Gavin | Comments | Related: > Database Design

Problem

You have one or more columns on a SQL Server table that are obsolete and no longer needed that you need to remove.

Solution

Columns are dropped with the ALTER TABLE TABLE_NAME DROP COLUMN statement. The following examples will show how to do the following in SQL Server Management Studio and via T-SQL:

Drop a column
Drop multiple columns
Check to see if a column exists before attempting to drop it
Drop column if there is a primary key or foreign key constraint on it

Note: Dropping a column permanently deletes the column and its SQL data.

SQL Server Versions used in this SQL Tutorial

Microsoft SQL Server 2019 (RTM-CU8)
SQL Server Management Studio 18.8

Setting Up Example

To get started I've created two new tables in MyDatabase. One is called Employees and the other called EmployeeStatus. Populated both with some sample data with various data types. Lastly, added a foreign key constraint on Employees that references EmployeeStatus.

-- use MyDatabase
USE [MyDatabase];
GO
 
-- create table [dbo].[Employees]
CREATE TABLE [dbo].[Employees](
   [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
   [LastName] [varchar](100) NOT NULL,
   [FirstName] [varchar](100) NOT NULL,
   [MiddleName] [varchar](100) NULL,
   [Title] [varchar](50) NULL,
   [TitleOfCourtesy] [varchar](20) NULL,
   [BirthDate] [date] NULL,
   [HireDate] [date] NULL,
   [TerminationDate] [date] NULL,
   [RehireDate] [date] NULL,
   [Address] [varchar](100) NULL,
   [City] [varchar](100) NULL,
   [Region] [varchar](100) NULL,
   [PostalCode] [varchar](100) NULL,
   [Country] [varchar](50) NULL,
   [HomePhone] [varchar](100) NULL,
   [Extension] [varchar](10) NULL,
   [Notes] [varchar](max) NULL,
   [ReportsTo] [int] NULL,
   [PhotoPath] [varchar](500) NULL,
   [EmployeeStatusID] [int] NULL,
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED([EmployeeID])
);
GO
 
-- insert some sample employee values 
INSERT INTO [dbo].[Employees]
VALUES('Smith','William','A','CEO','Bill','1960-01-01','1990-07-01','','','100 Main St','Boston','MA','02210','USA','123-456-7890','','','','',1),
      ('Jones','Thomas','J','IT Director','Tom','1950-02-28','1999-08-15','','','25 Main St','Cambridge','MA','02138','USA','123-678-9012','','','','',2);
GO
           
 
-- create table [dbo].[EmployeeStatus]
CREATE TABLE [dbo].[EmployeeStatus](
   [StatusID] [int] IDENTITY(1,1) NOT NULL,
   [StatusName] [varchar](50) NULL,
 CONSTRAINT [PK_EmployeeStatus] PRIMARY KEY CLUSTERED([StatusID])
);
GO
 
-- insert some sample employee status values
INSERT INTO [dbo].[EmployeeStatus]
VALUES('Currently Employed')
,('Retired')
,('Terminated')
,('Medical Leave');
GO
 
-- add foreign key to employees referencing employee status
ALTER TABLE [dbo].[Employees]  
ADD CONSTRAINT [FK_Employees_EmployeeStatus] FOREIGN KEY([EmployeeStatusID])
REFERENCES [dbo].[EmployeeStatus]([StatusID]);
GO
 
SELECT * FROM [dbo].[Employees]
SELECT * FROM [dbo].[EmployeeStatus]

Here is what are two tables look like.

SQL DROP COLUMN Example in SSMS

Here we'll drop the column ReportsTo from the Management Studio Object Explorer:

Expand database
Expand table
Expand column
Right click on column to drop
Delete

Or run the SQL command in the following statement:

-- drop ReportsTo Column from Employees table
ALTER TABLE [dbo].[Employees] DROP COLUMN [ReportsTo];
GO
 
SELECT * FROM [dbo].[Employees];
GO

DROP COLUMN Examples - Multiple Columns

To drop the PhotoPath and Notes columns using Object Explorer would require dropping each column individually as we've seen above.

To run the T-SQL directly is just a matter of putting the column names after the DROP COLUMN separated by commas and multiple columns can be dropped in one step with the ALTER TABLE statement.

-- drop PhotoPath and Notes columns from Employees table
ALTER TABLE [dbo].[Employees] DROP COLUMN [PhotoPath], [Notes];
GO
 
SELECT * FROM [dbo].[Employees];
GO

Check if column exists before dropping with ALTER TABLE DROP COLUMN IF EXISTS

It's likely you don't want your script to fail and generate an error. To avoid that it will be necessary to check to see if the column exists first.

Here, we'll check to see if the Extension column exists.

In the Object Explorer:

Right click on Columns
Refresh

The column Extension existing in the refreshed list is verification that it exists. To drop the Extensions column, we simply right click on it and delete as before.

There are a couple of ways to drop it programmatically with T-SQL depending on which version of SQL Server you are on.

The first way which will work on all supported versions is to check the sys.columns view to see if it exists first and run ALTER TABLE DROP COLUMN only if it does.

-- drop column Extension if it exists  - all sql server versions
IF EXISTS(SELECT 1 
          FROM sys.columns 
          WHERE name='Extension' AND  
          OBJECT_ID = OBJECT_ID('[dbo].[Employees]'))
ALTER TABLE [dbo].[Employees] DROP COLUMN [Extension]; 
GO
 
SELECT * FROM [dbo].[Employees];
GO

Had the column not existed the DROP COLUMN would just be ignored.

SQL Server 2016 and later adds the ability to add IF EXISTS to ALTER TABLE DROP COLUMN that checks for the column's existence and dropping it if it does all in one line of T-SQL.

-- attempt to drop column HomePhone using IF EXISTS to see if it exists – SQL Server 2016 and up ALTER TABLE [dbo].[Employees] DROP COLUMN IF EXISTS [HomePhone]; 
GO
SELECT * FROM [dbo].[Employees];
GO

Now, if we run the same statement to drop the column we know is no longer there the statement will complete without attempting to drop the column.

-- attempt to drop column HomePhone using IF EXISTS to see if it exists – SQL Server 2016 and up
ALTER TABLE [dbo].[Employees] DROP COLUMN IF EXISTS [HomePhone]; 
GO

DROP COLUMN Clause if there is a primary or foreign key constraint on it

So far there have not been any constraints on the columns dropped. But what happens if we try to drop a column that has a foreign key relationship to a column on an existing table?

Attempting to drop the EmployeeStatusID column from the Object Explorer will display an error as shown below.

Click the hyperlink under the Message column

And the following error is shown:

To drop the foreign key:

Expand Keys
RI Right click on the key to delete
Delete

Then go back and delete the column.

The same error is generated executing the T-SQL.

-- attempt to drop column EmployeeStatusID from Employees
ALTER TABLE [dbo].[Employees] DROP COLUMN [EmployeeStatusID]; 
GO

First, drop the key with an ALTER TABLE DROP CONSTRAINT and then drop the column with an ALTER TABLE DROP CONSTRAINT.

-- drop foreign key from Employees
ALTER TABLE [dbo].[Employees]  
DROP CONSTRAINT [FK_Employees_EmployeeStatus];
GO
 
-- drop column EmployeeStatusID from Employees
ALTER TABLE [dbo].[Employees] DROP COLUMN [EmployeeStatusID]; 
GO
 
SELECT * FROM [dbo].[Employees];
GO

Next Steps

Here some links to further information on table and column operations.

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