So you need to change a column name in your database. Maybe you messed up the name from the start, or your team decided the name needs to change for business reasons.
Either way, it’s got to change.
Luckily, the process to change a column name is not too difficult in SQL Server. In this tutorial, we’ll walk through the process and discuss some of the extra work you might need to do if you do, in fact, need to change a column name.
Do you need to change a column name in SQL Server? Learn how in this tutorial!
We’ll talk about the following topics:
- It cannot be done through the ALTER COLUMN statement
- The SP_RENAME system stored procedure
- Other uses of SP_RENAME
- Consequences of renaming a column
- Tips and tricks
Let’s begin.
1. It cannot be done through the ALTER COLUMN statement
Real quick, we need to talk about the ALTER COLUMN statement. You might think the ALTER COLUMN statement is what you would use to change the name of a column. After all, you are, in fact, altering the column if you rename it, right?
Wrong.
The ALTER COLUMN statement is meant to modify things like the column’s data type (from INT to BIGINT for example) or it’s nullability (from NULL to NOT NULL for example).
So if you’ve been trying to change a column name via ALTER COLUMN, we need to change your paradigm.
2. The SP_RENAME system stored procedure
The SP_RENAME system stored procedure is what we use to change a column name. It’s very easy to use.
There are only 3 parameters to this stored procedure:
1. @objname:
This is the name of the column we want to change. When we specify the column name, we need to do so using the two part identifier. That means we include the name of the table too. For example, ‘Sales.EmplID’. The table is Sales, and the column within that table (the column we want to change) is EmplID. When we specify a value for this parameter, we wrap it around single quotes.
2. @newname:
This is of course the new name we want to give our column. When we specify the new name, we do not use the two part identifier. We wrap the value around single quotes, too.
3. @objtype:
The last parameter is the type of object we want to change. In our case, the value for this parameter will simply be the word ‘COLUMN’. We’ll see in the next section how SP_RENAME can be used for other objects too, like indexes or a database name. Like the other parameters, notice this is also wrapped around single quotes.
An example of using SP_RENAME
Ok, let’s see it all in action.
Let’s create a very simple table called Customers:
CREATE TABLE Customers ( CustID INT IDENTITY, FName VARCHAR(15), LName VARCHAR(15) )
Now let’s add some data:
INSERT INTO Customers (FName, LName)
VALUES
('Joshua', 'Porter'),
('Andrew', 'Bluefield'),
('Jack', 'Porter')
Let’s check the data:
Ok, so let’s think about our column FName. Maybe that is a pretty poor name for a column. When we name objects in SQL Server, we should really try to be as descriptive as we can be. It’s usually better to have a more descriptive name than a short name. We’ll change the name of the column to FirstName.
The call would look like this:
EXECUTE SP_RENAME @objname = 'Customers.FName', @newname = 'FirstName', @objtype = 'COLUMN'
Remember the rules about the parameters:
- You need to use the two part identifier for the @objname parameter.
- You use the one part identifier for the new name of our column.
- The @objtype value in our case is simply the word ‘COLUMN’.
- All parameter values are wrapped in single quotes.
So let’s see it in action:
Notice the warning we get. We’ll talk about that soon 🙂
So let’s run our SELECT statement again to see the new name of our column:
Nice. Let’s do the same thing for the LName column. We’ll change it to LastName:
Easy peasy.
3. Other uses of SP_RENAME
The SP_RENAME procedure can change more than just column names. There are several objects in SQL Server that can be renamed using this procedure.
For the other objects, you would simply specify the appropriate @objtype value. Here is the full list of @objtype values you can specify, which indicates all the different objects you can change by using SP_RENAME. This is pulled directly from the official Microsoft documentation:
- COLUMN
- DATABASE
- INDEX
- OBJECT
- STATISTICS
- USERDATATYPE
This stored procedure is definitely a good one to know.
4. Consequences of renaming a column
Ok, here’s the thing about renaming a column:
You need to make sure other objects that reference the column are updated.
Let’s think about an example. Let’s create a very simple View that pulls the distinct LastName values from our Customers table:
CREATE VIEW DistinctLastNames AS SELECT DISTINCT LastName FROM Customers
Here’s us creating the View and running a quick query against the View:
Ok, now let’s run SP_RENAME to change the LastName column to CustomerLast:
EXECUTE SP_RENAME @objname = 'Customers.LastName', @newname = 'CustomerLast', @objtype = 'COLUMN'
Now let’s try to run the query against the View:
Uh Oh. Here’s that full error message:
Invalid column name ‘LastName’.
Msg 4413, Level 16, State 1, Line 32
Could not use view or function ‘DistinctLastNames’ because of binding errors.
The procedure will not update places where the column is referenced.
If we take a look at the definition of the View, we see it still says LastName:
So if you update a column name, you need to find every place that column name is referenced and change it.
Does that sound like a lot of work?
Yep.
This is why you should take your time when you first create your tables, columns, indexes, etc. It becomes much harder to change these things later, after they are well established in the database.
There is a helpful query you can run to find all objects that reference a specific word. That word can, of course, be a column name that you intend to change. Check out the following tutorial to see the query!
How to find text in any database object: Just run this one query!
5. Tips and tricks
Here are some tips you should know about how to change a column name in SQL Server:
Tip # 1: The SP_RENAME procedure returns 0 if the name was changed successfully
This is good to know for error-handling purposes. If the procedure successfully changed the name of your object, it returns 0. Otherwise, if the change was unsuccessful, it returns a nonzero value.
Tip # 2: Remember, we don’t use ALTER COLUMN
We don’t use the ALTER COLUMN statement to change a column name. This statement is reserved for changing things like a column’s data type or it’s nullability.
We use the SP_RENAME procedure to change a column name (as well as several other objects in SQL Server).
Tip # 3: Remember the rules about the parameters to SP_RENAME
Here are the rules we talked about:
- You need to use the two part identifier for the @objname parameter value.
- You use the one part identifier for the @newname parameter value.
- The @objtype value in our case is simply the word ‘COLUMN’.
- All parameter values are wrapped in single quotes.
Next Steps:
Leave a comment if you found this tutorial helpful!
Do you know how to change a table name in SQL Server? Learn how!
What about changing a column’s data type? You need to know!
Also, we touched on the topic of Views in SQL Server. Learn all about them here:
What is a View in SQL Server? A Guide for Beginners
Functions are also objects that will not be changed automatically when you change a column name. Like a View, you would also need to go to the definition of the function and change the column name. Learn all about functions here:
User Defined Functions in SQL Server: A Complete Guide
Thank you very much for reading! I hope this tutorial has helped you.
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, or if you are struggling with a different topic related to SQL Server, I’d be happy to discuss it. Leave a comment or visit my contact page and send me an email!
