Problem with Renaming a Column in SQL Server

问题:

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?

回答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.

回答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.

回答3:

To fix this:

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

回答4:

Run following queries together.

USE [DATABASE_NAME];

GO
EXEC sp_rename '[SCHEMA_NAME].[TABLE_NAME].OLD_COLUMN_NAME', 'NEW_COLUMN_NAME', 'COLUMN';
GO

It will resolve your problem