SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Renaming a Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

One of the things I rarely do is rename objects. There are good reasons to do so, but often the changes required in other objects and applications isn’t worth the hassle. This is one reason why it would be good to spend a few minutes in design and come up with good names from the beginning.

In any case, do you know how to do this? You could use SSMS and easily change the design of the table. Of course, SSMS might try to rebuild the table, which might not be what you want. Hopefully that’s not the case, though you should always get the script instead of just saving the change.

The code you’d like to see is a simple meta data change that uses sp_rename. In my case, I want to change Qty to Quantity. I’d use this code:

EXEC sp_rename @objname = ‘Sales.OrderLines.Qty’ ,
@newname = ‘Quantity’ ,
@objtype = ‘column’;

I wish we had a direct ALTER TABLE statement that worked here, or better yet, an ALTER TABLE that allowed the entire table code to be shown (that’s not coming), but I’m not holding onto any hope that Microsoft will change this.

If you’re a person that thinks you might need a temp table that you insert data into and then two renames of the tables, that’s not the best way. Simple meta data changes are always preferred.


I ran into this while helping someone test a change and thought this was a good, easy reminder of how to change names. You could show you understand this in a blog in five minutes.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...