SQL Drop Column Examples


By:   |   Updated: 2021-04-15   |   Comments   |   Related: More > T-SQL


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.

Setting Up

SQL DROP COLUMN Example in SSMS

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

  1. Expand database
  2. Expand table
  3. Expand column
  4. Right click on column to drop
  5. Delete
Drop Column - SSMS
  1. OK
Confirm Drop Column - SSMS

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 - T-SQL

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
Drop Multiple Columns - T-SQL

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:

  1. Right click on Columns
  2. Refresh
Check for Column Existence - SSMS

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
Drop Column if it exists - T-SQL

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
Drop Column if it exists - T-SQL

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 if it exists - T-SQL

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.

  1. Click the hyperlink under the Message column
Drop Column Failed - SSMS

And the following error is shown:

Drop Column Failed Error Message - SSMS

To drop the foreign key:

  1. Expand Keys
  2. RI Right click on the key to delete
  3. Delete
Drop Foreign Key - SSMS
  1. OK
Drop Foreign Key - SSMS

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
Drop Column Failed - T-SQL

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
Drop Foreign Key and Column - T-SQL
Next Steps

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



Last Updated: 2021-04-15


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





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