How do I rename a column?

2

April 18, 2016 by Kenneth Fisher

Everyone makes mistakes right? And sometimes you create an object and decide later that you messed up and need to rename it.

EXEC sp_rename 'OldName','NewName'

This works for tables, stored procedures, views etc, but there are a few things to be careful about. It doesn’t change the code behind code based objects so you need to modify that as well. And of course any time you use sp_rename you’ll get the warning:

Caution: Changing any part of an object name could break scripts and stored procedures.

This is a good warning, and the longer you’ve had the object around before you change it’s name the more likely that there will be one or more pieces of code that will have to be changed to match the new name.

But right now we are worried about changing the single column of a table. The same warning applies. The longer the column has been around the more likely you will have code that needs to be changed to match. So you want to do this as soon as you possibly can.

CREATE TABLE dbo.ChangeCol (Col1 int, oops int);
GO

We still use sp_rename but we have to specify both the table name and column name in the first paramter. The second parameter is just the new column name.

EXEC sp_rename 'dbo.ChangeCol.oops','Col2';
GO

2 thoughts on “How do I rename a column?

  1. This little stored proc has been the cause of so many problems. Stored Procs and user scripts are prime candidates to stop working.

    For the former I hope that refactoring options catch up to other languages.

    • Unfortunately name changes (of any type) are going to cause code problems. My guess is that’s always going to be on the developer to fix.

      The unfortunate part with sp_rename is that it doesn’t change the code behind SPs, Functions etc. So even though the name is changed in the system views the code itself still shows the old (wrong) name.

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 2,134 other followers

Follow me on Twitter

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