How to Drop or Delete a Single Column or Multiple Columns From an Existing Table in SQL Server

Tutorialsrack 18/02/2021 SQL SQL Server

In this article, you will learn how to drop or delete a single column or multiple columns from an existing table in an SQL server. Sometimes, you need to remove a single or multiple unused or obsolete columns from an existing table. To do this, you need to follow the syntax as given below:

Syntax For Delete Single Column

ALTER TABLE table_name
DROP COLUMN column_name;

Where,

• table_name is the name of the table from which you want to delete the column. Specify the table name in place of table_name
• Column_name is the name of the column that you want to delete. Specify the column name in place of column_name

If you want to delete more than one column at once, here is the syntax for deleting multiple columns.

Syntax For Delete Multiple Columns

ALTER TABLE table_name
DROP COLUMN column_name_1, column_name_2,...;

If the column contains constraints or other dependencies. Then, an error message will be returned. Resolve the error by deleting the referenced constraints.

Examples

This SQL statement creates a new table named Employee in the Database:

CREATE TABLE Employee(
    id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255),
    city  VARCHAR(255)
);

Example 1: Dropping  a Single Column from a Table

The following statement drops the city column from the Employee table:

ALTER TABLE Employee
DROP COLUMN city;

Example 2: Dropping Multiple Columns from a Table

The following statement drops the date_of_birth, phone, email column from the Employee table:

ALTER TABLE Employee
DROP COLUMN date_of_birth, phone, email;

I hope this article will help you to understand how to drop or delete a single column or multiple columns from an existing table in an SQL server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts