Potential Issue with Re-Naming Stored Procedures

Question

Potential Issue with Re-Naming Stored Procedures

Viewing 7 posts - 31 through 36 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Moe M Old Hand Points: 364 More actions · Answer 1

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!

Thanks!

Yes it's good to always stick to the best practices.

Moe M
Database Consultant
http://www.cubeangle.com

Andrew Novick SSC Enthusiast Points: 102 More actions · Answer 2

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 SSCrazy Eights Points: 9708 More actions · Answer 3

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

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.

Thanks

Moe M Old Hand Points: 364 More actions · Answer 4

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

Thanks!

Yes it seems we have to deal with it our own way

Moe M
Database Consultant
http://www.cubeangle.com

Moe M Old Hand Points: 364 More actions · Answer 5

Hardy21 (10/10/2010)
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
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.

Thanks for the comment!

On production the story is totally different. Normally there are regulations and checks in place to make sure these sort of issues don't occur.

Moe M
Database Consultant
http://www.cubeangle.com

Peter Petrov SSCommitted Points: 1517 More actions · Answer 6

Nice finding! Good to know...

The script could be simplified (2005 and 2008):

SELECTOBJECT_NAME(object_id) AS proc_name

,object_id

,definition

FROMsys.sql_modules

WHEREPATINDEX('%CREATE%PROCEDURE%',definition) > 0

ANDPATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0

ORDERBY OBJECT_NAME(object_id);

Moe M Old Hand Points: 364 More actions · Answer 7

Peter Petrov (10/15/2010)
Nice finding! Good to know...
The script could be simplified (2005 and 2008):
SELECTOBJECT_NAME(object_id) AS proc_name
,object_id
,definition
FROMsys.sql_modules
WHEREPATINDEX('%CREATE%PROCEDURE%',definition) > 0
ANDPATINDEX('%' + OBJECT_NAME(object_id) + '%', definition) = 0
ORDERBY OBJECT_NAME(object_id);

Thanks Peter,

Yes you are right. As mentioned in the article in 2005 and 2008 you don't have the problem of record size limitation therefore it is much easier to extract the rouge procedures.

Moe M
Database Consultant
http://www.cubeangle.com