Problem with Renaming a Column in SQL Server

Question

So I was trying to rename a column in my table from Conversion_Fee_PerShare to just Conversion Fee.

I looked up online and found the syntax be:

sp_RENAME 'TableName.[OldColumnName]', '[NewColumnName]', 'COLUMN'

I wrote my query as:

sp_RENAME 'dbo.AllocationDetails.[Conversion_Fee_Per_Share]' , '[Conversion_Fee]', 'COLUMN'

The column name has now become [Conversion_Fee] instead of Conversion_Fee

Now if am trying to rename again like this:

sp_RENAME 'dbo.AllocationDetails.[Conversion_Fee]' , 'Conversion_Fee', 'COLUMN'

It gives me an error saying:

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

I tried to Alter Table Drop Column AllocationDetails.[Conversion_Fee] it didn't work that way either.

Whats the right syntax?

Answer 1

/*Initial Table*/  
CREATE TABLE AllocationDetails
  (
     Conversion_Fee_Per_Share FLOAT
  )

/*Faulty Rename*/  
EXEC sp_rename
  'dbo.AllocationDetails.[Conversion_Fee_Per_Share]',
  '[Conversion_Fee]',
  'COLUMN'

/*Fixed Rename*/  
EXEC sp_rename
  'dbo.AllocationDetails.[[Conversion_Fee]]]',
  'Conversion_Fee',
  'COLUMN'

DROP TABLE AllocationDetails 

The column name to use in the second sp_rename call is that returned by SELECT QUOTENAME('[Conversion_Fee_Per_Share]').

Alternatively and more straight forwardly one can use

EXEC sp_rename
  'dbo.AllocationDetails."[Conversion_Fee]"',
  'Conversion_Fee',
  'COLUMN'

QUOTED_IDENTIFIER is always set to on for that stored procedure so this doesn't rely on you having this on in your session settings.