SQL Server ALTER TABLE DROP COLUMN

In SQL Server, there could be some situations when we may have to delete or drop a column from a table. Sometimes the column in a table was created wrongly or maybe it is no longer required and has become obsolete. So, to drop a column from a table, the ALTER TABLE, DROP COLUMN SQL query is used. In this article, we will discuss how to drop a column from a table, and what needs to be taken care of while dropping a column.

ALTER TABLE DROP COLUMN

Syntax:

The Syntax for the ALTER TABLE DROP Column statement is as follows:

ALTER TABLE table_name

DROP COLUMN column_name1, column_name2;

Explanation:

This ALTER TABLE statement is a 2-part command for dropping a column.

First, we need to specify the Table Name, after the ALTER TABLE keyword and then give the Column name list after the keyword, DROP COLUMN. The column list can be a single column name or more than one column name separated by a comma (, ).

The ALTER TABLE DROP column command is explained below with few examples.

Example 1

Below is the structure of the ‘Students’ table, which is having 7 columns. In this table column name ‘Exam_Name’ was added by mistake and want that column to be removed from the table. In this scenario, I can use the ‘ALTER TABLE, DROP Column‘ command from SQL Server Management Studio.

Students Table

Below is the command to delete the column ‘Exam_Name’ from the ‘Students’ table.

ALTER TABLE Students 
DROP  COLUMN  Exam_Name

If the above command was successful then below is the output message received.

Commands completed successfully.

Below is the result of the above DROP column command. You can see that the ‘Exam_Name‘ is removed now and it has 6 columns only.

Students Table after the ‘DROP’ command

Example 2

Below is the structure of table ‘Students_Details’ used for example 2, for deleting multiple columns using the ‘ALTER TABLE DROP Column’ command. This table has initially 6 columns.

Table Students_Details

In example 2 using the above table ‘Students_Details’ we need to remove the 2 columns ‘Course_Details’ and ‘Parent_Details’, as for these 2 columns it was decided to create separate tables and remove them from this table.

So the below command will remove the 2 columns using the single command ‘ALTER TABLE DROP Column’. This command will remove multiple columns as below.

ALTER TABLE Students_Details
DROP  COLUMN  Course_Details, Parent_Details

If the above command was successful then below is the output message received.

Commands completed successfully.

After the above command is executed successfully, the 2 columns ‘Course_Details and Parent_Details’ are dropped from the table ‘Students_Details’ and the table will look as below with only 4 columns now.

Students_Details after 2 columns dropped

So from the above example, we can see how a table columns can be dropped in scenarios where we want to drop one or more columns.

Column Constraints and Drop Column

If we delete a column that has a column constraint, then the column cannot be deleted and an error will be displayed when we execute the ALTER TABLE, DROP COLUMN command.

Example:

ALTER TABLE Students 
DROP  COLUMN  Student_Age;

Error Message

Msg 5074, Level 16, State 1, Line 1

The object ‘CK_StudentAge’ is dependent on column ‘Student_Age’.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE DROP COLUMN Student_Age failed because one or more objects access this column.

In the above query to drop the column, the column name ‘Student_Age’ is specified, which has a column constraint. Due to this constraint, the execution of the SQL statement will fail with the error message.

So if we need to drop a column with a column constraint, we need to remove the column constraint first, and then only the column can be dropped from the table.

The below statement will remove the column constraint:

ALTER TABLE Students 
DROP CONSTRAINT CK_StudentAge

Now the below DROP column statement will work fine:

ALTER TABLE Students 
DROP  COLUMN  Student_Age

Notes:

The SQL statement ‘ALTER TABLE DROP Column’ will delete a column permanently and can not recover. Only option to recover is to recreate the column again.

Any table with data in the specified column, which is being dropped using the ‘ALTER TABLE DROP Column’ will delete all data from the specified column while dropping the column and the data can not be recovered unless there is a backup of data. So be very cautious while deleting columns.

Conclusion

In this article, the ‘ALTER TABLE DROP Column’ is explained in detail with examples of how to drop one or more columns from a Table. Also explains the situations when the DROP COLUMN may give an error and how to resolve the error.