Drop Column in SQL Server

SQL Server allows the user to delete one or more columns from the table whenever they are not used or obsolete. It is required to have ALTER permission on the object before removing the columns from a table. In this article, we are going to discuss a complete overview of how we can delete columns from an existing table.

Limitations and Restrictions

We cannot remove a column with a CHECK constraint in the table. If we want to drop a column with constraints, we first remove the constraint. SQL Server does not drop a column with PRIMARY or FOREIGN KEY constraints or other dependencies. However, we can do this by using the Table Designer. We will first delete all dependencies on the column while using Object Explorer or Transact-SQL to drop columns.

We can drop columns in SQL Server mainly in two ways:

1. Transact-SQL.
2. SQL Server Management Studio (SSMS)

Transact-SQL

SQL Server has ALTER TABLE DROP COLUMN command for removing columns from an existing table. We can use the below syntax to do this:

In this syntax,

• First, we will write the table name from which we want to remove columns.
• Second, we will write the column name that we are going to delete.

We can also drop multiple columns into the existing table at once. We can do this by specifying a list of columns separated by a comma that we would like to remove from a table after the DROP COLUMN clause. The below syntax explains it more clearly:

DROP COLUMN Example

This example explains how we can delete a column from the table in SQL Server. We will first create a table named "Users" using the below statement:

Our table structure looks like the below image where we have a total of seven columns:

Suppose we want to drop a 'City' column that is no longer required in the table. We can remove this column using the statement as follows:

Executing this statement returns the message shown in the below image:

In some cases, we want to delete more than one column from the table at once. For example, we can remove the 'Branch' and 'Email' columns from the 'Users' table by executing the below statement:

When the command runs successfully, we will get the below message:

As we can see in the table definition, the Mobile_no column has a CHECK constraint. Therefore, we cannot delete it. If we try to run the following command, we will get an error:

Here is the error message:

If we want to delete the Mobile_no column, we first need to delete its CHECK constraint using the below statement:

Now, we are able to delete the Mobile_no column. See the below output:

SQL Server Management Studio (SSMS)

SSMS is a windows software tool used to connect and work with our SQL Server from a graphical interface instead of using the command line. The management studio allows us to drop columns from the table in the following ways:

1. Drop a column using Table Designer
2. Drop a column using Object Explorer

Let us see each of them in detail.

Drop a column using Table Designer

The following steps are used to remove columns with Table Designer:

Step 1: In Object Explorer, go to the Databases -> Tables menu and expand it.

Step 2: Select the desired table from which we want to delete columns, right-click on it, and select the Design option from the context menu.

Step 3: Once we select the Design option, we will see the table in design mode like the below image.

Step 4: Right-click the column that we are going to delete and select the Delete Column option from the menu.

Step 5: If you get a prompt message to confirm your column's deletion and relationships, click the Yes button.

Drop column using Object Explorer

The following steps are used to delete a column using Object Explorer:

Step 1: Go to the Databases -> Tables menu and expand it.

Step 2: Select the desired table and expand it.

Step 3: Select the Columns and expand them.

Step 4: Right-click on the column name you want to remove and choose the Delete option.

Step 5: In the Delete Object dialog box, click OK.

NOTE: An error message will appear in the Delete Object dialogue box if the column contains constraints or other dependencies. Delete the referenced constraints to fix the issue.

Step 6: Refresh the database or table to finish the deletion steps.