how to delete column in sql

How to Add, Delete or Rename a Column in a Table Using SQL

One Click Feedback
Please help us to improve the site by rating the quality of this article by clicking a button below.

Excellent

Good

Average

Poor

Awful

In many circumstances columns in a table can be removed, added or renamed without having to drop and recreate the table.

By way of example I'll create a table and then populate it with a couple of rows of data :

CREATE TABLE Table1
(
Column1 INT NOT NULL,
Column2 VARCHAR (5) NULL,
Column3 VARCHAR (20) NOT NULL,
Column4 VARCHAR (50) NOT NULL,
Column5 DATETIME NOT NULL
)
GO

INSERT INTO Table1 (Column1,Column2,Column3,Column4,Column5)
SELECT 1, 'Mr' , 'Fred' , 'Bloggs' , '23 Dec 1962' UNION
SELECT 2, 'Mrs' , 'Freda' , 'Bloggs' , '10 Jan 1964'

Adding Columns

To add columns simply define the column name and datatype (e.g. VARCHAR(100)) with the following syntax :

ALTER TABLE Table1 ADD Column6 VARCHAR (100) NULL

There are some restrictions around adding columns, for instance the column has to be NULLable if it doesn't have a default defined.

Deleting Columns

A similar syntax to that above can be used to drop an existing column :

ALTER TABLE Table1 DROP COLUMN Column2

Renaming Columns

You can also rename an existing column, though curiously this is not supported by the ALTER TABLE syntax and instead we will need to use the sp_rename stored procedure :

exec sp_rename 'Table1.Column1' , 'ID' , 'COLUMN'
exec sp_rename 'Table1.Column5' , 'DateOfBirth' , 'COLUMN'

The last parameter specifies the object type to be renamed, in this case a column. There are circumstance where columns cannot be dropped or renamed, for instance you can't rename (or indeed delete) a column if it has a constraint defined on it. You'll notice the following warning message when running sp_rename in SQL Server Management Studio. However there's nothing to worry about, this is only a warning and the column will still get renamed!