How to find text/word inside SQL Server store procedure

Any of the following snippet allows you find a specific word or words from all SQL store procedure. This can be very useful if you want to find all references to table/specific word and remove/replace them. Below code should work on SQL server 2005/2008 (R2).

SELECT DISTINCT

        ( OBJECT_NAME(id) )

FROM    syscomments

WHERE   text LIKE '%Search_word%'

SELECT  ROUTINE_NAME ,

        ROUTINE_DEFINITION

FROM    INFORMATION_SCHEMA.ROUTINES

WHERE   ROUTINE_DEFINITION LIKE '%Search_word%'

        AND ROUTINE_TYPE = 'PROCEDURE'

      

SELECT  Name

FROM    sys.procedures

WHERE   OBJECT_DEFINITION(object_id) LIKE '%Search_word%'

   

SELECT  OBJECT_NAME(object_id)

FROM    sys.sql_modules

WHERE   Definition LIKE '%Search_word%'

        AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1