Knowing how to make modifications to the definition of a table is very important when it comes to developing Microsoft SQL Server databases.
The task of deleting a column is very straightforward. In this very brief tutorial, we’ll discuss how to drop a column from a Microsoft SQL Server table.
Run the DROP COLUMN statement in an ALTER TABLE statement
Let’s set up a very simple table to work through an example. Take the following Employees table:
CREATE TABLE Employees
(
EmplID INT PRIMARY KEY IDENTITY(50,10),
FirstName VARCHAR(20),
MiddleName VARCHAR(20) DEFAULT '-',
LastName VARCHAR(20),
EmployeeCode CHAR(4)
)
GO
INSERT INTO Employees (FirstName, MiddleName, LastName, EmployeeCode)
VALUES
('Michael', 'Douglas', 'Booth', 'MDB1'),
('George', 'Alexander', 'Gray', 'GAG1'),
('Rachel', 'Susanne', 'Washington', 'RLW1'),
('Morgan', 'Denise', 'Bennett', 'MDB2'),
('Richard', 'Eugene', 'Paulson', 'REP1')
GO
Here is what the content of your table should look like:
An example of dropping a column
Let’s say we want to drop the EmployeeCode column. Maybe we’ve decided we don’t need this column anymore. We drop this column by running the following very simple ALTER TABLE statement:
ALTER TABLE Employees DROP COLUMN EmployeeCode
If we run that statement, then query the table again, we see the column is gone!:
Some columns may be easier to drop than others
While the DROP COLUMN statement is very simple an easy to execute, keep in mind there are several scenarios where SQL Server will not let you drop a column.
For example, SQL Server will not let you drop a column if there is some kind of constraint on the column.
Notice the MiddleName column has a default constraint placed on it. Since, this constraint exists for this column, SQL Server will not let us run a DROP COLUMN statement on that column:
If we wanted to drop this column, we would need to drop the constraint first:
Nice.
And it can be any kind of constraint. We can’t drop the EmplID column either because it has a primary key constraint:
Again, we would need to drop this constraint before we could drop the column.
One of the more tricky constraints is the foreign key constraint. SQL Server won’t let you delete a column if it is part of a foreign key constraint, either. Luckily, I wrote a great query you can use to locate any foreign key references in a table.
Before you drop a column, you should know what objects reference that column
What if we want to drop a column that is referenced by a View or a user defined function? SQL Server isn’t smart enough to update those objects to change/remove that reference to the dropped column.
Let’s see what columns are left in our Employees table:
Let’s create a simple View that references the FirstName column:
SQL Server will let us drop the FirstName column even though it is referenced by that View:
But of course, if we try to query our View, we get an error message:
So before you drop a column, it is very important to make sure you know what objects reference that column. You’ll need to make sure you change/remove those references to keep those objects working correctly. This can be a very tedious task if your database has many objects, so I put together this great query to help you locate all instances of a particular word. In this case, the ‘word‘ would just be the name of the column you want to drop!
Next Steps:
Leave a comment if you found this tutorial helpful!
Do you know how to change the name of a column? Click the link to find out!
We talked a lot about constraints in this tutorial, too. You will probably find the following FREE Ebook very helpful:
FREE Ebook on SQL Server Constraints!
This FREE Ebook contains absolutely everything you need to know about all the different constraints available to us in Microsoft SQL Server, including:
- Primary Key constraints
- Foreign Key constraints
- Default constraints
- Check constraints
- Unique constraints
This Ebook will definitely be a great resource for you to keep and reference throughout your career as a data professional. Get it today!
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please leave a comment. Or better yet, send me an email!
