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:
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.