How do I find Stored Procedures with wrong text in syscomments

Question

There appears to be several stored procedures that were renamed with sp_rename, causing the syscomments to be left with the old proc name instead of the new proc name.

Thus, my db transfers are failing to transfer correctly.

Is there a way to find which procs (or any objects) have their names out of synch?

Or better, a simple way to find AND fix them?

Answer 1

You could try

select object_name(id)
from syscomments 
WHERE COLID=1 AND
OBJECTPROPERTY(id, 'isprocedure')=1 and
REPLACE(REPLACE(TEXT,CHAR(13),' '),CHAR(10),' ') 
          NOT LIKE '%CREATE%PROC%'+ object_name(id) + ' %'
AND
(REPLACE(REPLACE(TEXT,CHAR(13),' '),CHAR(10),' ')
          NOT LIKE '%CREATE%PROC%'+ object_name(id) + ']%')

SYS.SQL_MODULES would probably be slightly more robust if there is a risk that you've got some 4,000 character comment before the CREATE PROC bit.

A more robust solution than parsing the text yourself though would be to generate a script to run to recompile all your stored procedures. If you run the below...

SELECT 'EXEC sp_refreshsqlmodule ''' + 
            quotename(schema_name(schema_id)) + '.' + 
            quotename(object_name(object_id)) + '''' 
FROM sys.procedures

...then copy and paste the results into a management studio query window and execute the resultant script it will give you an error for ones where the names are out of synch.

This will cause all your stored procedure plans to be recompiled so don't do this on a production server if that's an issue.