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

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

From stackoverflow

• 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.

  TLiebe : Thanks to KM for fixing up the SQL code and making it look presentable.

  OMG Ponies : heh - tonnes of whitespace, but none for the CHARINDEX comparison

  KM : @rexem, all I did was to highlight the code block and click on the 1s & 0s icon to make the text fixed width, I'm not going to fix the white space and indents...

• 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>%'
  

  OMG Ponies : Didn't know about the 4000 character limit on INFORMATION_SCHEMA.ROUTINES.

  vas : Msg 208, Level 16, State 1, Line 2 Hi Remus! When running the script getting the error :- Invalid object name 'sys.sql_modules'. Msg 208, Level 16, State 1, Line 2 Invalid object name 'sys.objects'. The script was 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'%GetExcpCTSInfo%'

  Remus Rusanu : You did mark your question sql-server-2005. Looks like you're testing on SQL 2000.

  vas : 100 % sure it is SQL 2005

  Remus Rusanu : You realize I did not hack up MSDN and put in place the documentation of those metadata views. Check @@version, then check if you have some minimal rights in the database (VIEW DEFINITION).

• sasi_checkkeywordinSP 'SearchKey'