Caveat of sp_rename

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: