casinos

RENAME’ing Objects October 14th, 2010

Vinod Kumar

At a number of places people have asked me this simple requirement of how can I go about renaming objects inside SQL Server. This can be to rename a Column, Table or even a Database.

There are multiple scenario’s, like in a bulk uploading table you want to push the data into a staging table and then move data after cleaning. During next upload you don’t want the previous uploaded data but want to have it for reference. In this case, this renaming helps tons.

Sometime there are cases, where you want to change the schema like renaming a column like “Phone” to “Telephone” or “Cell” but don’t want to add another column or get into migration of data. This is a neat way to work around this.

sp_rename [ @objname = ] 'object_name' , 
[ @newname = ] 'new_name' 
[ , [ @objtype = ] 'object_type' ] 
More about this lesser known command can be got from Documentation. 
Note: Use it with utmost care as this involves Schema change directly !!!

Share this article

Tags: , , , , ,

This entry was posted on Thursday, October 14th, 2010 at 08:30 and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


One Response to “RENAME’ing Objects”

  1. Nakul Vachhrajani says:

    Hello!

    First up, let me thank-you, Vinod for regularly enlightening the community with you insights. I don’t know if you recall me or not, but we have met in Ahmedabad, Gujarat; when you were here to attend one of the Community Tech Days event. I am looking forward to meeting you once again in the near future.

    Next, I would like to add a word of caution for all readeers. sp_rename comes with it’s own share of issues, and as is the case with every other command, one needs to use utmost care when using this.
    The following SSC article highlights one such issue – http://www.sqlservercentral.com/articles/syscomments/70931/

    This article demonstrates that while sp_rename is good for most operations, please do not use this to rename Stored Procedures and other such objects – the best way for these objects is to drop & recreate.

    Per BOL (http://msdn.microsoft.com/en-us/library/ms188351.aspx):
    “Changing any part of an object name can break scripts and stored procedures. We recommend you do not use this statement to rename stored procedures, triggers, user-defined functions, or views; instead, drop the object and re-create it with the new name.”

    Thank-you!

Leave a Reply



 

Email
Print