SQL Server Drop Column

We typically start by creating the schema for the data we wish to store in a relational database. This allows us to pre-define our requirements before creating data. However, data is dynamic, and even carefully defined schemas may change, leading to invalid data.

The kernel of this tutorial is to give you the basics of how to drop a column from an existing table in SQL Server. Removing columns can be beneficial as it allows you to clean your database and remove unwanted information from the database.

SQL Server Drop Column

To remove a column from a database table in SQL server, use the ALTER TABLE followed by the DROP COLUMN query.

The syntax is as shown:

SELECT database_name;
ALTER TABLE TABLE_NAME
DROP COLUMN column_name, column_name_2, column_name_3...;

We start by selecting the target database. Next, we define the table’s name to drop the specified column or columns. Lastly, we specify the names of the columns we wish to drop from the table.

Before deleting a column, ensure it does not have a CHECK constraint. If a column contains a CHECK constraint, SQL Server will require you to remove the constraint before dropping a column.

NOTE: Removing columns with PRIMARY or FOREIGN key constraints is not allowed in SQL Server.

Drop Column – Example

To illustrate using the SQL Server Drop column query, let us use a real-world database, such as the SalesDB sample database. You can download and import the database from the resource below:

https://www.dropbox.com/s/zqg8lv20ivf0lzj/salesdb%28linuxhint%29.zip?dl=0

The example query below deletes the MiddleInitial column from the Employees table of the sample database provided in the resource above.

USE salesdb;
ALTER TABLE Employees
DROP COLUMN MiddleInitial;

Since the column does not have any constraints, SQL Server successfully removes the column from the specified table.

Drop Column – Example 2

SQL Server returns an error if you try to remove any column with PRIMARY KEY constraint, as shown in the example query below:

USE salesdb;
ALTER TABLE Employees
DROP COLUMN EmployeeID;

Since the EmployeeID column has a PRIMARY KEY constraint, SQL Server returns errors as:

Msg 5074, Level 16, State 1, Line 2
The object 'EmployeePK' IS dependent ON COLUMN 'EmployeeID'.
ALTER TABLE DROP COLUMN EmployeeID failed because more objects accessed this COLUMN.

Drop Column – Example 3

To drop multiple columns from a database, specify them as comma-separated queries as shown in the example:

USE salesdb;
ALTER TABLE Employees
DROP COLUMN FirstName, LastName;

If the specified columns exist on the table, the SQL server will remove them as specified in the query.

Drop Column – SSMS

You can also delete a column using the SQL Server Management Studio. Start by selecting your target database -> Expand to tables -> Expand target table and select columns. Expand the columns menu and select your target column. Right-click and select the delete option.

Conclusion

In this article, you learned how to drop columns from a database table in SQL Server using Transact-SQL and SQL Server Management Studio.

Thanks for reading!

from https://ift.tt/3fk6rel