SQL Server ALTER TABLE DROP Column

Learning Objective

The objective of this SQL Server tutorial is to teach you how to use SQL Server ALTER TABLE DROP Column to remove one or more columns from the existing table.

Introduction to SQL Server ALTER TABLE DROP Column

While working in the SQL Server database, sometimes, you may need to remove one or more columns from an existing table. SQL Server provides the ALTER TABLE DROP Column statement to remove column(s) from a table.

SQL Server ALTER TABLE DROP Column Syntax

The following syntax will remove a column from an existing table.

ALTER TABLE table_name
DROP COLUMN column_name;

In this syntax,

  • table_name – Specify the name of the table from which you want to remove column(s).
  • column_name – Name of the column you want remove.

The following syntax will remove multiple columns using a single ALTER TABLE statement by specifying the column names by comma-separated.

ALTER TABLE table_name
DROP COLUMN column_name_1, column_name_2,…;

Please note that if you want to delete a column having a CHECK constraint, you must first remove the constraint before removing the column. Also, SQL Server does not allow remove column having PRIMARY KEY and FOREIGN KEY constraint.

SQL Server ALTER TABLE DROP Column Examples

Let’s create a table named employees for our example.

CREATE TABLE employees
(
emp_no INT IDENTITY PRIMARY KEY,
first_name varchar(50) NOT NULL,
last_name varchar(50) NOT NULL,
dob date NOT NULL,
joining_date date NOT NULL,
dept varchar(50),
salary INT CONSTRAINT ck_positive_salary CHECK(salary >=0),
father_name varchar(100),
medical_history varchar(250)
);

The following statement remove medical_history column from employees table.

ALTER TABLE employees
DROP COLUMN medical_history;

The column salary has a CHECK constraint on it, so you cannot remove the column without removing the constraint. The following statement gives an error.

ALTER TABLE employees
DROP COLUMN salary;

You will see below message,

The object 'ck_positive_salary' is dependent on column 'salary'.

To drop the salary column first remove the CHECK constraint from the column.

ALTER TABLE employees
DROP CONSTRAINT ck_positive_salary;

And, then remove the salary column.

ALTER TABLE employees
DROP COLUMN salary;

The following statement drop two column at once using a single ALTER TABLE DROP Column statement.

ALTER TABLE employees
DROP COLUMN father_name,dept;

Summary

In this tutorial, you have learned how to use SQL Server ALTER TABLE DROP Column to remove one or more columns from the existing table.

Advertisement