I was using sp_rename lately and came across this article that goes into the pitfalls.
You should also be aware that if you generate a creation script for a renamed stored procedure using Query Analyzer or sp_helptext, then the generated script will not create the stored procedure that is in the database. It will create one that has the original name.
Specifically, if you rename a procedure and then grab the sp_helptext output and re-run that command you might not be recreating the same procedure at all.
That is ridiculously stupid. I am pretty sure I haven't seen people use it for that purpose though (more for index or table renames, etc) but just to be sure I whipped up a quick and dirty script to check:
Select * From sys.sysobjects Join sys.syscomments On sysobjects.id = syscomments.id And syscomments.colid = 1 Where sysobjects.xtype In ('P', 'FN', 'IF', 'TF') And syscomments.text Not Like ('%create%' + Case When sysobjects.xtype In ('P') Then 'proc' Else Case When xtype In ('FN', 'IF', 'TF') Then 'function' End End + '%' + sysobjects.name + '%')
It's not very good and would fail if you use the old function name somewhere else in the procedure. But for a quick check: