- Script SP1 out as CREATE
- Right click on SP1 on the left panel and rename it to SP1_backup
- Work with the script and when done with the changes execute it to create the new SP1
With SQL Server 2008 Microsoft decided to eliminate this little annoyance, so now step 3 above will work just fine, the new SP1 is created successfully and you will be happy until you decide to look into SYS.sql_modules and realize that you now have two SPs with identical names in the definition. Fine, that’s not the end of the World you say. But just for a little fun try executing the sp_refreshsqlmodule 'dbo.SP1_Backup' - this should be completely harmless, simply updates the outdated metadata right? Well, try looking into SYS.sql_modules again after you execute that – surprise, all the work you did on SP1 just disappeared! Now, the definition of the SP1 is exactly the same as that of SP1_backup. Definitely not what you wanted or expected!
The renaming issue has been there forever – they (Microsoft) must have a pretty good reason for not fixing it, so I am assuming it will never be fixed. In the meantime the advise to SSMS user is: when it comes to SPs, UDFs and Views just pretend that the rename functionality does not exist – if you want to rename such an object script it out, change the name in the CREATE statement and execute the script; then just drop the original object.