"sp_rename ... 'COLUMN'" - Renaming an Existing Column in SQL Server

Q

How to rename an existing column with the "sp_rename" stored procedure in SQL Server?

✍: FYIcenter.com

A

If you have an existing column in a table and you want to change the column name, you can use the "sp_rename ... 'COLUMN'" stored procedure. "sp_rename" allows you to change names of COLUMN, DATABASE, INDEX, USERDATATYPE, and OBJECT. The tutorial example below shows you how to rename a column:

USE master
GO

sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213
Either the parameter @objname is ambiguous or the claimed 
   @objtype (COLUMN) is wrong.

USE FyiCenterData
GO

sp_rename 'tip.subject', 'title', 'COLUMN'
GO
Caution: Changing any part of an object name could break 
   scripts and stored procedures.
   
SELECT id, title, description, author FROM tip
GO
id  title      description               author
1   Learn SQL  Visit dev.fyicenter.com   NULL

You are getting the first error because 'FyiCenterData' is not the current database.

 

Managing Tables and Columns in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-17, 810👍, 0💬