Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Think while Rename a Column Name of Table in SQL.

  • 21 May 2013
  • Author: Anil
  • Number of views: 10298
  • 0 Comments

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.

columnrename

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.

Thanks,

Anil Maharjan

Print
Categories: Analysis Services
Tags:
Rate this article:
No rating
Anil

AnilAnil

Highly motivated Business Intelligence Engineer having leadership abilities and team work skills as well as the ability to accomplish tasks under minimal direction and supervision. Has more than 5 years of development & implementation experience in HealthCare Data Analytics and Telecommunication

Other posts by Anil

Full biography Contact author

Please login or register to post comments.