If someone tries to rename the column of table then we write a query as
SP_RENAME 'TableName.[OldColumnName]' , '[NewColumnName]', 'COLUMN' --we use as SP_RENAME 'DimCustomer.[FirstName]' , 'CustomerName' , 'COLUMN' --Mistakenly, SP_RENAME 'DimCustomer.[FirstName]' , '[CustomerName]' , 'COLUMN' which gives the column the name [CustomerName], not CustomerName. For rename the column http://blog.sqlauthority.com/2008/08/26/sql-server-how-to-rename-a-column-name-or-table-name/
You don’t use the  escape in the second argument but by mistakenly if we use ‘[CustomerName]’ as new column name instead of CustomerName.
It sucessfully updates the column but while we select that column it’s now gives the error as
"Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong"
Also while we try to drop the column itself we cannot delete it. It also gives error as
ALTER TABLE DROP COLUMN failed because column '[CustomerName]' does not exist in table 'Customer'.
Does that mean we have to recreate the customer table since the renamed column [CustomerName] cannot use in select statement too.
Well there is a method for changing the mistakenly rename column ‘[CustomerName]’ to CustomerName . Just simple you need to go to the table DimCustomer and then click plus sign of the table and then you can see the list of columns of DimCustomer table then right click and add New Column tab which will edit all the list of columns and there you go just change the ‘[CustomerName]’ to CustomerName . This will solve the issue we are having.
If someone by mistakenly rename the column as above and getting similar issue this post will be surely helpful.
Also, there might be any alternative T-SQL method then please suggest some idea that will surely helpful.
One thought on “Think while Rename a Column Name of Table in SQL.”
Meanwhile if we use the query as
select [[CustomerName]]] from DimCustomer it will select the column.
I couldn’t get how SQL database table stores the column in such format.?
Also, while rename if we use script as
SP_RENAME ‘DimCustomer.[FirstName]’ , [CustomerName] , ‘COLUMN’
It works but by mistake if we use as above then this post will surely helpful.