Don’t rename SPs, UDFs and Views – drop and create instead

When developers at client X need to make changes to an SP (let’s call that SP1) they usually go through those simple steps: 

1. Script SP1 out as CREATE
2. Right click on SP1 on the left panel and rename it to SP1_backup
3. Work with the script and when done with the changes execute it to create the new SP1

If you try this in 2005 you would get an error telling you that the SP1 already exists in the database even though it was just renamed to SP1_backup! There is a perfectly good explanation for this on a Microsoft kb article – basically when you rename an object like a stored procedure, user defined function or view the name of the object in the catalog is changed but the name of the object in the definition of the object is not changed! While this is annoying, at least it stops the user from making a complete mess.

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.