How to simply rename a column

Question

How to simply rename a column

Dom 941

I thought the simple way to rename a column in SQL server was

ALTER TABLE TableName

RENAME COLUMN OldColumnName TO NewColumnName;

This results in

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near 'RENAME'.

Did something change or am I doing something wrong? I ended up just going into Design... on the table and simply typed over the name, but I thought this was not the "programmer's" way, lol.

Accepted answer

0 additional answers

Your answer

Answer 1

Naomi Nosonovsky 8,431

Try using sp_rename procedure, e.g.

EXEC sp_RENAME 'dbo.test.oldColumn', 'newColumn', 'COLUMN'

Dom 941 Reputation points

2023-03-28T21:28:41.5233333+00:00

Yeah I found that out in google world too. Just seems a lot less straightforward. Was there a reason MS made the change? Thanks for your help.
Erland Sommarskog 121.5K Reputation points MVP Volunteer Moderator

2023-03-28T21:36:00.3033333+00:00

MS has not made any change. There has never been any ALTER TABLE RENAME COLUMN in SQL Server. sp_rename is a very old thing, and something Microsoft inherited from Sybase.

I agree that ALTER TABLE RENAME COLUMN is cleaner, but sp_rename works with more than columns: tables, stored procedures, types and a few more things. I guess that Microsoft has never found it compelling enough to replace the procedure with proper syntax for all these cases.

If you feel strongly about this, you can file a suggestion here: https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0

Share via

How to simply rename a column

0 additional answers

Your answer