SQL Server Performance Forum – Threads Archive

Are there any known issue using sp_rename?

We plan to use sp_rename to rename many columns of our tables. Before we do that, I want to know whether there is any known issue using sp_rename.
Thank you.

You have two potential issues: 1) The application code or t-sql that access the tables need to be changed.
2) If you have any indexes, primary keys, unique keys, or foreign keys, they need to be dropped, and then readded, after the columns have been renamed. If you don’t do this in the correct order, you will get lots of nasty error message. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com

Hi Brad, BOL of SQL Server 2000 states this: sp_rename automatically renames the associated index whenever a PRIMARY KEY or UNIQUE constraint is renamed. If a renamed index is tied to a PRIMARY KEY constraint, the primary key is also automatically renamed by sp_rename.

I don’t know of any issues with sp_rename except the first one Brad mentioned. This is the recommended way and gets you even going where other methods fail like this onehttp://www.insidesql.de/content/view/269/ –Frank
http://www.insidesql.de

Potentially I can see the point (1) raised by Brad will be an issue and as stated by BOL it is true. But as you’re planning on most of your tables means always test before deploying it in the production. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Frank, Is that a way to translate thehttp://www.insidesql.de/content/view/269/ into English?
Thank you.

Well…….it doesn’t do a very good job, but you might be able to decipher the idea from this: http://translate.google.com/transla…=UTF-8&ie=UTF-8&oe=UTF-8&prev=/language_tools MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Unfortunately, the link is broken. You’ll have to copy and paste it into your browser.
Anyway, some time ago someone asked a question how to rename a column prefixed with a #. You can easily create a table with such a column, however when you try to drop that column in EM you’re out of luck. Strangely it seems you can’t even rename it with EM. Neither are you able to DROP with column via QA. The only way that worked, was to use sp_rename to rename that column and DROP it thereafter.
Well, that’s the short summary of the text. One shouldn’t think too much about the intention of that poster when creating that column, however what I meant with this was that sp_rename does a good job when all else fails.
–Frank
http://www.insidesql.de

Permissions are not exactly an issue <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />, but you should always remember about them. From BOL: Members of the sysadmin fixed server role, the db_owner and db_ddladmin fixed database roles, or the owner of the object can execute sp_rename. Only members of the sysadmin and dbcreator fixed server roles can execute sp_rename with ‘database’ as the object_type. – Marek ‘chopeen’ Grzenkowicz, MCP Poland

derrickleggett, Thank you for the link. I just copy the link starting from http… Frank, The # problem is an interesting issue. Marek, Thank you reminding me about the permissions. Thank you, Peter

]]>