Home

Transact-SQL: Deleting a Column

 

Introduction

To delete a column using code, first open or access an empty query window, and use the following formula:

ALTER TABLE TableName
DROP COLUMN ColumnName

On the right side of the ALTER TABLE expression, type the name of the table. On the right side of the DROP COLUMN expression, enter the name of the undesired column. Here is an example:

ALTER TABLE StaffMembers
DROP COLUMN CurrentResidence;
GO

When this code is executed, the interpreter will look for a column named CurrentResidence in a table named StaffMembers of the current or selected database. If it finds that column, it will remove it from the table.

Microsoft SQL Server can also generate sample code you can use to delete a column from a table. Before doing this, first display an empty query window and display the Templates Explorer. Expand the Table node. In the Table section, drag Drop Column and drop it in the query window. Delete the undesired sections of code and keep only the part that deals with adding a column. Here is an example:

--============================================
-- Drop column template
--
-- This template creates a table, then it  
-- drops one of the columns of the table.
--============================================
USE <database, sysname, AdventureWorks>
GO

-- Drop a column from the table
ALTER TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
	DROP COLUMN <new_column_name, sysname, column3> 
GO
 

Home Copyright © 2008-2011 FunctionX.com