search for a string in a stored procedure

• messageforums

  Grasshopper

  Points: 13

  More actions

  October 13, 2004 at 5:38 pm

  #165135

  hi I have a very large database with several stored procedures. now, I need to find a stored procedure that does some calculation if that stored procedure has a string "MESSAGE"(just an example) in an IF statement somewhere down the procedure. currently i am opening every stored procedure, doing a Ctrl+F for the above string and if i find it then that means i determine that this is the stored procedure that i will have to work with(change the calculation in it or some work) so my question is that can i write something ... that will list me all the stored procedures which have a certain string contained in them?? I am new to sql stuff .. so please help me. this could save me a lot of time. thanks -messageman

• anna docwra

  SSC Rookie

  Points: 48

  More actions

  October 13, 2004 at 7:09 pm

  #526618

   

  -- exec proc sp_Find_in_Proc('searchstring")

  CREATE proc sp_Find_in_Proc

  @SeachString varchar(255) = NULL

  AS

  IF  @SeachString is Null

     Begin

      Print 'Missing Parameter @SeachString!'

      return

     end

   

  DECLARE @Pattern as varchar(255)

  SELECT @Pattern = '%"' + @SeachString + '%'

  Print '  '

  Print 'Seaching for ' + @Pattern + ' in procedures....'

  Print '  '

  Print ' Found in procedures...'

  SELECT name FROM syscomments,sysobjects

    WHERE OBJECTPROPERTY(sysobjects.id,N'IsProcedure')=1

     And sysobjects.id = syscomments.id

     AND PATINDEX(@Pattern , text) !=0

     ORDER BY Name

• CAGreensfelder

  SSC Eights!

  Points: 919

  More actions

  October 15, 2004 at 7:48 am

  #526896

  We have been using:

  -- sp_grep   v1.0 03/16/1995, v1.1 10/26/1995, 

  -- Author:   Andrew Zanevsky, AZ Databases, Inc.

  -- E-mail:   zanevsky@azdatabases.com

  A co-worker found it at

  http://www.sqlservercentral.com/scripts/contributions/1201.asp

  It will find a string almost anywhere in the database - column names, stored procedures, triggers...  Works great.

   

• Bill Burkett

  Old Hand

  Points: 328

  More actions

  October 15, 2004 at 10:23 am

  #526929

  Try this query.

  select distinct o.name

  from syscomments c , sysobjects o

  where c.text like '%base%' and

  c.id = o.id and

  o.xtype = 'p'