How to Add, Modify, Rename, and Delete a Column in SQL

There has been a few times where I needed to compose a script to update a customer’s database, and I’ve had to use probably the most popular of the SQL ALTER TABLE syntax: Add, Modify, Rename, and Drop. To help save for my own notes and to possibly give some help,  I thought I’d write a quick article on these 4 things.

Syntax

Add a Column

To add a column to the table :

ALTER TABLE table_name
ADD column_name datatype

Modify a Column

To change the datatype of a column in the table :

ALTER TABLE table_name
ALTER COLUMN column_name datatype

Rename a Column

To rename a column name in the table :

EXEC sp_rename ‘tableName.[oldColumnName]‘, ‘newColumnName’, ‘COLUMN’

Delete a Column

To remove the column from the table :

ALTER TABLE table_name
DROP COLUMN column_name

Example

Say we have a table Person which holds some basic information. We are going to show examples of how to do the previous four functions, but wrapped in a If Not Exists or something similar to be able to put into a script that we can run multiple times, and always give us the outcome we desire.

We now realize we want to add a column Address.

IF (SELECT COLUMNPROPERTY(OBJECT_ID('Person'),'Address','IsIdentity')) IS NULL
   BEGIN
	ALTER TABLE Person
	ADD Address VARCHAR(64)
   END
GO

Now that we have added that column, lets go ahead and change the datatype of the Phone to hold more data.

IF NOT (SELECT COLUMNPROPERTY(OBJECT_ID('Person'),'Phone','IsIdentity')) IS NULL
   BEGIN
	ALTER TABLE Person
	ALTER COLUMN Phone VARCHAR(32)
   END
GO

After updating phone we decide however that we want to be a little more descriptive, and make Phone into PhoneNumber.

IF NOT (SELECT COLUMNPROPERTY(OBJECT_ID('Person'),'Phone','IsIdentity')) IS NULL AND
       (SELECT COLUMNPROPERTY(OBJECT_ID('Person'),'PhoneNumber','IsIdentity')) IS NULL
   BEGIN
	EXEC sp_rename 'Person.Phone', 'PhoneNumber', 'COLUMN'
   END
GO

Finally we look at our table and decide we don’t want to include the address, so let’s go ahead and drop the column

IF NOT (SELECT COLUMNPROPERTY(OBJECT_ID('Person'),'Address','IsIdentity')) IS NULL
   BEGIN
	ALTER TABLE Person
	DROP COLUMN Address
   END
GO

There you have it! Now you can write a script to add, modify, delete, or rename columns in your database. Good luck and happy coding!