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.