Adding, altering or removing multiple columns from a table

2

April 16, 2015 by Kenneth Fisher

I was recently asked to do some fairly heavy modifications to a table. It involved adding 6 new columns, removing 4 old ones, renaming 4 and then altering the datatype for those 4. I find these kinds of requests rather entertaining in a simplistic sort of way. I try to do as little typing (no GUI though) as possible to get the task done. So as a challenge, I thought I’d give a table and some changes required, then you see how few commands you can get it done with. I’ll give my solution as well, but before looking give it a shot yourself.

CREATE TABLE ChangeMe (
	ChangeMe_Id INT NOT NULL IDENTITY (1,1),
	FirstName varchar(50),
	LastName varchar(50),
	Phone1 char(10),
	Phone2 char(10),
	Phone3 char(10),
	Phone4 char(10),
	Salary money,
	LastBonus money,
	LastReview decimal(5,2),
	ManagerComments varchar(max)
	)

The changes that need to be made are the following:

Add
     Address1 varchar(255)
     Address2 varchar(255)
     City varchar(50)
     State char(2)
     Zip char(10)

Drop
     Salary
     LastBonus
     LastReview
     ManagerComments

Change
     Phone1 to HomePhone varchar(12)
     Phone2 to CellPhone varchar(12)
     Phone3 to WorkPhone varchar(12)
     Phone4 to PagerPhone varchar(12)

Here are the rules. No creating a new table and copying the data over. On the columns that need to be changed they actually have to be changed, not dropped and new ones created.

Give it a shot, then come back and check my answer. See if you can do it in less commands than I have. And try to keep the typing down. Remember copy and paste is your friend. If you do come up with something faster/easier please put it in the comments, I’d love to see it.
 
 
 
 
 


So here is my solution.

-- Rename 4 columns.  Four commands.
EXEC sp_rename 'ChangeMe.Phone1','HomePhone'
EXEC sp_rename 'ChangeMe.Phone2','CellPhone'
EXEC sp_rename 'ChangeMe.Phone3','WorkPhone'
EXEC sp_rename 'ChangeMe.Phone4','PagerPhone'

-- Alter 4 columns.  Four commands
ALTER TABLE ChangeMe ALTER COLUMN HomePhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN CellPhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN WorkPhone varchar(12)
ALTER TABLE ChangeMe ALTER COLUMN PagerPhone varchar(12)

-- Add 5 columns. One command.
ALTER TABLE ChangeMe ADD
	Address1 varchar(255),
	Address2 varchar(255),
	City varchar(50),
	State char(2),
	Zip char(10)

-- Drop 4 columns. One command
ALTER TABLE ChangeMe DROP COLUMN 
	Salary,
	LastBonus,
	LastReview,
	ManagerComments

-- Total 10 commands.

Renaming a column of course requires the sp_rename stored procedure. It only renames one column at a time so the best we can do is one command per column. Alter is the same way. ALTER TABLE ALTER COLUMN only works on one column at a time (at least as best I could find out). I did find it interesting that while you can only do one ALTER COLUMN per command you can ADD or DROP as many columns as you want in a single command. Only ADDs or DROPs in a single command though.

Personally I find this more “interesting T-SQL trivia” than something earth-shattering but it is handy to know at times. I’m a firm believer in knowing every bit of T-SQL I can because you never know when you will need it.

2 thoughts on “Adding, altering or removing multiple columns from a table

  1. Kevin Wilkie says:

    In the case of the change of datatypes, if you are going from a large datatype to a smaller datatype, I have found that SQL Server keeps creating new rows taking up the size originally stated.

    For example, if you have a database table that you need to convert a field from varchar(50) to char(2) — because they might at some point in the future change the 2 character abbreviation of states to a much longer format — then even after the change, the database will still create new rows using the varchar(50) file size.

    I don’t know of any way to correct that except for moving it to a new table. Is there one?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,671 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: