SQL Server 2012 – How to Change Column Name

sql-server-2012

I'm needing to update a few column names in some tables I have in my database. I'm using SQL Server 2012.

I'm aware of using the RENAME ability: 

ALTER TABLE table_name
   RENAME COLUMN old_name to new_name;

or by using the SQL Server Management Studio by pulling up the Table's Design and then just updating the name for the column and saving the changes.

What I need to know is if either of these drops the column in question. I don't want to lose any of the data, I just want to rename the column name to something else.

Best Answer

I would not recommend SQL Server Management Studio's Table Designer, since in my experience, it will create a new table, move all the data, then drop the old table and rename the new table.

The syntax for renaming a column is not to ALTER TABLE, but to use sp_rename:

EXEC sp_rename 'table_name.old', 'new_name', 'COLUMN';

http://msdn.microsoft.com/en-us/library/ms188617(v=sql.110).aspx

The sp_rename stored procedure only changes the name and does not manipulate the data.

EDIT: Code corrected per shadonar.

Related Question