Problem with Renaming a Column in SQL Server

Solution 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.

Solution 2:

Never mind I found out:

ALTER TABLE dbo.AllocationDetails
DROP COLUMN [[Conversion_Fee]]]

OR

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

these will work fine. :)

Using Double Quotes:

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

will also work.

Solution 3:

To fix this:

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