How to simply rename a column

Dom 776

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

Naomi Nosonovsky 7,856 Reputation points

2023-03-28T21:23:39.1+00:00

Try using sp_rename procedure, e.g.

EXEC sp_RENAME 'dbo.test.oldColumn', 'newColumn', 'COLUMN'
Please sign in to rate this answer.

1 person found this answer helpful.
Dom 776 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 110.4K Reputation points MVP

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
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer

Share via

How to simply rename a column

0 additional answers

Your answer