Rename only affects the “sysobjects” table and the “syscommnets” table is not getting updated with the change.
One might think that the solution is to somehow refresh or update the “syscomments” table with the new procedure name, however it is neither possible nor recommended to update the system catalogue tables manually or with an ad hoc query. Even if it was allowed, it is too risky an operation to try. So basically the only solution that is left is to drop the procedure and recreate it with the new name.
Note
As it is mentioned in the comments of the that post if you are using SQL Server 2005 you won’t face this problem since you can use the OBJECT_DEFINITION system function that takes care of all these for you.
Note 2
PROC/Trigger/etc source is stored in the syscomments table as an NVARCHAR(4000), when you have a PROC that exceeds 4000 characters, the source spans multiple rows. Very occasionally this-multi row split occurs right in the middle of the string you are searching for. To overcome this we need to concatenate the rows, returning the full string for each id (avoiding loops and cursors wherever practical).
Advertisement
