Friday, June 09, 2006

Rename A column In A Table With sp_rename

How do you rename a column in a SQL Server table without using Enterprise Manager/SSMS of course, T_SQL only.
This is a frequent question in the newsgroups and it's causing some confusion for people because you would assume that

you would use the ALTER TABLE syntax, instead of that you have to use sp_rename to rename the column

--Create the table
CREATE TABLE TestRename (id INT,[Some[[Col] INT)

--Do a select to check the column name
SELECT * FROM TestRename

-- This is how you change the dataype

--this is how you rename the column
EXEC sp_rename 'TestRename.[Some[[Col]', 'SomeCol', 'COLUMN'

--Let’s check again, you will see that the column name has changed
SELECT * FROM TestRename

After changing the column name with sp_rename you will get the following warning
Caution: Changing any part of an object name could break scripts and stored procedures.
The COLUMN was renamed to 'SomeCol'.

No comments: