How to rename a column in SQL?

Question

What is the best practice when it comes to renaming a table column using SQL (MS SQL Server 2005 variant)? This assumes that there is data in the column that must be preserved.

Answer 1

You have to use a stored proc to rename a column. The following will rename your column from 'oldColumnName' to 'newColumnName' without affecting any data.

EXEC sp_rename ‘tableName.[oldColumnName]‘, ‘newColumnName’, ‘COLUMN’

Obviously you'll have to update any code / stored procs / SQL that uses the old name manually.