SQL Server Performance Forum – Threads Archive

how does sp_rename work?

Hi,
I tried sp_rename to rename the stored procedure in the database and it was renamed to the new name. But, when I scripted out or used sp_helptext, it was showing me the old stored procedure name. For example, sp_rename ‘abc’, ‘cia’. when i did the sp_helptext ‘cia’, it was showing me the create proc dbo.abc (in QA – sql 2000). Another interesting thing, when I opened/scripted out this sp as Create/Edit in SQl Server Management Studio(SQL 2005), it shows me create proc dbo.cia BUT when I do as sp_helptext, it shows me create proc dbo.abc again in Management Studio. Any idea why it is happening?
Dan

You can change the name of an object or data type in the current database only. The names of most system data types and system objects cannot be changed. Could be your case?
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirÃ¡ luego de tu muerte
All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Try to refresh the information on the GUI tools. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya,
You mean the object explorer(browser)? I did refresh the stored procedure and it shows me new SP name in both environment but sp_helptext always shows as the old name. Regarding the database, I was changing this in current database. I am thinking now it is better to drop and recreate the SP instead of renaming it. Any suggestion? Thanks to LuisMartin also. Dan

i would imagine that sp_name is simply UPDATE sysobjects SET name = ‘new’ WHERE name = ‘old’ but the contents of the sp are in in the text field of syscomments, including the ‘CREATE old AS…’
are not changed

Just for the curious. Since sp_rename is not an extended procedure, you can analyse what it is doing by
sp-helptext ‘sp-rename’ You’ll see that Joe’s correct. Sorry, I seem to have problems again with one of these sp_ names again. Replace the ‘-‘ with underscores. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks everyone for your reply. I will never trust sp_rename to rename objects(specially for SP) unless someone has valid reason to do it.? Thanks Dan

i think you are missing the point, sp_rename is fine,
just be aware of what is happening underneath
esp for sprocs and functions

…and sp_rename is the only officially supported way. Apart from DROP and re(CREATE). —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Yes my concern was sp_rename for SP and Functions since table and other object rename seem to be getting what I expected. I believe now as long as the application is not pointing to that proc name (once we change the sp name), it should be ok with the sp_rename. My whole thing of this conversation was to clean up the Obsolete SPs(believed to be Unused SP) from database. Is there any other way to BACKUP the SPs and delete them from database? Thanks guys for your time.
Dan

use the generate script feature in EM, its good that you are hunting down dead procs, i have had people leave them around for years try also first removing execute privileges, see who complains

That’s a good idea Joechang. It’s hard to identify and remove the old procs from a legacy system since we don’t know what are they(most SPs)using for.
Dan

]]>