davepaulino (10/7/2010)

---

We also encountered this problem when we generate using sql compare application. Our workaround is to drop and recreate the procedure or functions. Great article!

Andrew Novick (10/9/2010)

---

Yes, it's a problem. I submitted an item on Connect
https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com

Andrew Novick (10/9/2010)

---

Yes, it's a problem. I submitted an item on Connect
https://connect.microsoft.com/SQLServer/feedback/details/398203/sp-rename-doesnt-change-the-text-of-udfs-or-procs-sp-helptext-then-returns-a-script-with-the-old-name
and it was classified as "Won't Fix"

It's something we're going to have to live with.

Regards,
Andy
Andrew Novick
SQL Server MVP
http://www.NovickSoftware.com

Hardy21 (10/10/2010)

---

Thanks author for nice article.
I many times used sp_rename stored procedure but it is for our testing database. In production work, we always drop/create procedure with Grant so we did not face this issue, but good to know that if we are using 'syscomments' instead of 'sys_modules' anywhere in any script, it cause an issue.

Peter Petrov (10/15/2010)

---

Nice finding! Good to know...

The script could be simplified (2005 and 2008):

SELECT	OBJECT_NAME(object_id) AS proc_name
,	object_id
,	definition
FROM	sys.sql_modules
WHERE	PATINDEX('%CREATE%PROCEDURE%',definition) > 0
AND	PATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0
ORDER	BY OBJECT_NAME(object_id);