How to Drop Columns in MS SQL Server

Sometime you may need to delete one or more unused or obsolete columns from your existing table. To do this, you need use the ALTER TABLE DROP COLUMN statement as follows:

To delete single column:

ALTER TABLE table_name
DROP COLUMN column_name;

In the above syntax:

• First, you have to specify the name of the table from which you want to delete a column.
• Second, specify the name of the column that you want to delete from the table.

To delete multiple columns:

ALTER TABLE table_name
DROP COLUMN column 1,column 2,...column N;

In the above syntax:

• First, you have to specify the name of the table from which you want to delete a column.
• Second, specify the columns separated by coma that you want to delete from the table.

Let’s create a table for a demonstration the statement:

CREATE TABLE [DataAnalytics].[dbo].[Agents](
    agent_id bigint,
    name varchar(30) NOT NULL,
    father_name varchar(30) NOT NULL,
    mother_name varchar(30) NOT NULL,
    dob date NOT NULL,
    joining_age DEC(10,2) NOT NULL CONSTRAINT chk_joining_age CHECK(joining_age >= 18),
    PRIMARY KEY(agent_id)
); 

The following statement drops the dob column from the [DataAnalytics].[dbo].[Agents] table:

ALTER TABLE [DataAnalytics].[dbo].[Agents]
DROP COLUMN dob;

The following statement drops the father_name, mother_name columns from the [DataAnalytics].[dbo].[Agents] table:

ALTER TABLE [DataAnalytics].[dbo].[Agents]
DROP COLUMN father_name, mother_name;

But if you want to delete a column with CHECK constraint you have delete the constraint first then you have to execute drop statement otherwise, you will get an error message. For example:

ALTER TABLE sales.price_lists
DROP CONSTRAINT chk_joining_age;

ALTER TABLE [DataAnalytics].[dbo].[Agents]
DROP COLUMN joining_age;

In this tutorial, I have shown you how to delete column from MS SQL Server using ALTER TABLE DROP COLUMN statement from an existing table. Hope you have enjoyed the tutorial. If you want to get updated, like my facebook page http://www.facebook.com/freetechtrainer and stay connected.