search for a key word in all the Stored Proc's for a given DB?

Question

Hello All! How to do a global search for a key word in all the Stored Proc's for a given DB?

I used the following but I am unable to get the desired results...

SELECT name 
FROM sysobjects 
WHERE id IN 
  ( 
    SELECT id 
    FROM syscolumns 
    WHERE name = 'loadStatus' COLLATE SQL_Latin1_General_CP1_CI_AS 
  ) 

SELECT name 
FROM sysobjects 
WHERE id IN 
  ( 
    SELECT id 
    FROM syscolumns 
    WHERE name like '%loadStatus%' COLLATE SQL_Latin1_General_CP1_CI_AS
  )

Regards -Vas

Answer 1

Have a look at this link - http://www.sqlservercentral.com/scripts/T-SQL+Aids/31131/ and see if that helps.

The SP in the linked article was written by Prasad Bhogadi and is as follows:

CREATE PROCEDURE SEARCHFORSTRING @SEARCHSTRING VARCHAR(100)
AS
      SELECT  DISTINCT(sysobjects.name)
             FROM 
       sysobjects,syscomments
      WHERE 
       sysobjects.id =     syscomments.id
      AND 
       sysobjects.type = 'P'
      AND 
       sysobjects.category=0
AND
     CHARINDEX(@SEARCHSTRING ,syscomments.text)>0

You can use this to create an SP in your database or adapt it for a one-time query.

Answer 2

Only sys.sql_modules contains the whole definition of the procedure. Other views like INFORMATION_SCHEMA.ROUTINES contain a truncated definition.

select object_name(o.object_id), m.*
from sys.sql_modules m
join sys.objects o on m.object_id = o.object_id
where o.type= 'P'
and m.definition like N'%<keyword>%'

Answer 3

sasi_checkkeywordinSP 'SearchKey'