How to drop a column from an existing table in SQL Server? Example Tutorial

Hello guys, In the last article, I have shown you how to add a new column to an existing table in Microsoft SQL Server and today, I'll teach you how to remove or drop a column from an existing table in SQL server. Dropping a column is a trivial task from a new table e.g. you can just use ALTER TABLE table_name DROP COLUMN column_name to drop a column but when it comes to removing a column from existing table, you need to be more careful because it may contain data or it may be referenced from other tables. If a column is associated with another table using foreign key constraint then you cannot remove it until you remove the foreign key constraints. 

So, before removing a column from existing table you need to perform due diligence to find out all its dependencies, which is much larger task then just executing ALTER statement to drop a column.

In this article, I will not only teach you the SQL statement i.e. ALTER table statement to drop a column but you'll also learn the right way to do it. 

Since, when you remove a column from existing database in production, you need to prepare SQL script, it is also useful to write defensive queries so that it can also run on database which are not in sync i.e. it may or may not contain the column you are trying to remove it.

SQL Query to delete a column from existing table in SQL Server

You can use DROP COLUMN clause in ALTER table statement to remove a column from a existing table in Microsoft SQL Server. 

ALTER table statement is used to modify structure of table and it is used to add, remove or update columns in a table. If you remember, in last article we had used

ALTER TABLE table_name ADD column_name datatype constraint

to add a column like shown in following example

ALTER TABLE Books ADD reviews VARCHAR(MAX) NULL

will add a new column reviews into existing table Books. The column name is "reviews" and its a VARCHAR type and can be NULL. 

But, the SQL to remove a column is slightly different than this i.e. you need to specify COLUMN keyword to state that you are removing columns like

ALTER TABLE table_name DROP COLUMN column_name

Remember, we have to specify that we are removing column and column_name but nothing else e.g. datatype and constraints are not required. For example, following SQL will remove the "reviews" column added into Books table before:

ALTER TABLE Books DROP COLUMN reviews;

This will remove the "reviews" column from Books table. 

SQL Script to remove a column from existing table

Above example is ok for test database and when you are removing the column by yourself, but if you have to remove a column from existing table in production environment and if you don't have permission to access production database then you have to rely on DBAs to do the job for you. 

In order to ask DBAs to remove column, you need to prepare a SQL script for them to be executed against the designated database and table. 

Here is a snippet of SQL Script you can use to first check if a column exists and then DROP it using the ALTER TABLE statements.

USE [Library]
GO
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
   WHERE TABLE_NAME = 'Books' AND COLUMN_NAME = 'reviews') 
BEGIN
PRINT 'deleting reviews from Books'
ALTER TABLE Books DROP COLUMN [reviews]
END
ELSE
BEGIN
PRINT 'reviews column not found on Books table'
END

This SQL script first ensure that we are executing this SQL on Library database by using EXISTS clause. It then checks if Books table has a column with name "reviews", it then print that you are deleting the review column from Books table and then it actually deletes it using the same SQL we have seen before. If doesn't found the specific column it just print the column is not found.

Btw, if you are using Microsoft SQL Server 2016 version then you can use DIE (DROP IF EXISTS) feature to replace above SQL script with following single line SQL query:

ALTER TABLE table_name DROP COLUMN IF EXISTS column_name

i.e. you can re-write our SQL scripts as shown below:

ALTER TABLE Books DROP COLUMN IF EXISTS [reviews]

This query is idempotent i.e. you can run this query multiple times and it won't throw any error. The column will be removed if exists i.e. the first time you execute and later it won't execute the DROP statement. 

So, if you are lucky to run on SQL Server 2016 then instead of using the big IF clause, you can use this DIE statement to remove a column from existing table. 

Important points about removing column from existing table with data

As I said before, dropping a column from a new table or unused table is a trivial task but dropping a column from an existing table with data and dependencies are very difficult. It requires a lot of effort and due diligence to find out dependencies and then only you can remove the column. 

Here are a couple of useful tips which you can use while deleting a column from an existing table:

1. If the column you are removing is referenced by other tables then you need to figure out what to do with other tables/columns. You can either remove foreign keys and keep referenced data in other tables, or you can find all referencing columns and remove them as well if they are not needed any longer.

In such cases the real challenge is finding all the tables which are dependent upon this columns. Thankfully, there are a couple of third party tools you can use e.g. Red Gate Dependency tracker to find all foreign keys. 

2. If there is a constraint applied on the column you are removing form table then you have to remove the constraint before you drop the column. You can use following SQL to remove the constraint from a column in SQL Server

ALTER TABLE table_name DROP CONSTRAINT {constraint_name_on_column_name}

Once you do, you can execute ALTER table to drop the column itself. 

3. You can also remove a column from an existing table using Microsoft SQL Server Management Studio (SSMS), good thing about that is it will warn you if the column you are removing has been referenced elsewhere. Here are the steps to drop a column from an existing table using SQL Server managed Studio.

Put table in Design view (right click on table) like so:

Right click on column in table's Design view and click "Delete Column"

That's all about how to remove a column from existing table in Microsoft SQL Server. As I said, removing a column is very simple, you just need to execute ALTER TABLE command with DROP e.g. ALTER TABLE table_name DROP COLUMN column_name but the real problem starts when you remove column from existing column because it may contain data or it may be referenced from other database and tables. 

You cannot remove a column if there is another table where it is a foreign key. You either need to remove the foreign key constraint, if you want to keep data or you have to remove that column as well before you delete the column from primary table.

Other Frequently asked SQL queries from Interviews

• How to join three tables in one SQL query? (solution)
• How to find the 2nd highest salary of an employee in SQL? (answer)
• How do find all table names in a database? (query)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• How do you create a backup of the table or copy of the table using SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• Can you write a pagination query for Oracle using row_number? (query)
• Top 5 Courses to learn MySQL Database for Beginners (Courses)
• 10 Frequently asked SQL Query interview questions (solution)
• Difference between Self and Equi Join in SQL? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• 4 ways to find the Nth highest salary in SQL (solution)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Write a SQL query to find all table names on a database in MySQL (solution)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• How do you find Nth highest salary of an employee using the correlated query? (solution)
• Difference between Primary and Candidate key in table? (answer)
• How do you find all customers who have never ordered? (solution)
• Difference between Unique and Primary key in table? (answer)
• What is the difference between View and Materialized View in Database? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)

Thanks for reading this article so far. If you like this SQL ALTER command tutorial to remove column from existing table then please share it with your friends and colleagues If you have any questions or feedback then please drop a note. 

P.S. - If you are new to the SQL and looking for free SQL and database courses to learn SQL fundamentals then you can also check out my list of free Udemy courses to learn SQL. These are really nice SQL courses that are available for free on Udemy and Coursera and you can use them to build your SQL skills.