store procedure

• markwnorsoft

  SSC Eights!

  Points: 827

  More actions

  September 6, 2005 at 9:54 am

  #152532

  Hi,

  I'm running sql server 2000.  I have a couple hundred store procedures.  I was wonderding if there was a way to look for a word in the store procedure without opening all the store procedures.  Like a find in notepad or if anyone knows where the store porcedures are store.

  Thanks

  Mark

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  September 6, 2005 at 9:56 am

  #587837

  Select DISTINCT O.Name from dbo.SysObjects O inner join dbo.SysComments C on O.id = C.id and O.XType = 'P' WHERE C.Text like '%SearchString%'

• gbn

  SSCertifiable

  Points: 6429

  More actions

  September 6, 2005 at 9:57 am

  #587838

  DECLARE @codesnippet varchar(100)

  --Amend here to insert wht you are looking for

  SET @codesnippet = 'wantedword'

  SELECT DISTINCT LEFT(DB_NAME(), 20), (LEFT(OBJECT_NAME([id]), 40)) AS ObjectName

          FROM dbo.syscomments

          WHERE [text] LIKE '%' + @codesnippet + '%' AND OBJECTPROPERTY([id], 'IsMSShipped') = 0

          ORDER BY ObjectName

   

  This is my routine, includes triggers and other objects as well

• Steve-3_5_7_9

  SSChampion

  Points: 10832

  More actions

  September 7, 2005 at 6:28 am

  #588050

  free tool has been posted on this site many times.  SQL Digger

  http://sqldigger.bdsweb.be/

• nmederich

  Old Hand

  Points: 347

  More actions

  September 7, 2005 at 8:35 am

  #588106

  Got a simple little stored proc here that does a nice job of grabbing the proc name and the snippet of code containing the search term.

  I also created one that looks at views as well with a simple code change.

  Enjoy

  CREATE      proc  dbo.sp_ProcTextSearch

  (

   @search_string varchar(128)

  )

  AS

  DECLARE @strSQL varchar(350)

  DECLARE @strSQL2 varchar(300)

  DECLARE @dbname sysname

  DECLARE @x varchar(257)

  DECLARE @count int

  SET NOCOUNT ON

  BEGIN

  /*-------------------------------------------------------------------

  ** Procedure Name:  dbo.sp_ProcTextSearch

  **   Date Written:  10/03/2002

  **-------------------------------------------------------------------

  **  Input Parameters: 

  **     @search_string = string to search on

  **      Input Tables: 

  **     none (just procedures)

  ** Output Parameters: 

  **     Output Tables: 

  **     Return Status: returns table name and line where text is found 

  **--------------------------------------------------------------------

  ** Comments: 

  **

  **-------------------------------------------------------------------*/

  -- GET ALL PROC NAMES INTO #ONE

  SELECT b.[name] + '.' + a.[name] as spname

    INTO #one

    FROM sysobjects a INNER JOIN sysusers b

      ON a.uid = b.uid

   WHERE xtype = 'p'

  DECLARE cone CURSOR FOR

   SELECT spname

     FROM #one

  -- Proc text table

  CREATE TABLE #spcount (sptext varchar(1000))

  -- complete proc name and text table

  CREATE TABLE #final (spname varchar(128), sptext varchar(1000))

  OPEN cone

  FETCH NEXT FROM cone INTO @x

  WHILE @@FETCH_status=0

    BEGIN

      -- Get the text

      TRUNCATE TABLE #spcount

      SET @strSQL = 'insert #spcount EXECUTE sp_helptext ''' + @x + ''''

      EXECUTE(@strsql)

      -- Populate the table #final with proc name and the text

      INSERT #final

         SELECT @x, sptext

         FROM #spcount

      FETCH NEXT FROM cone INTO @x 

    END

  CLOSE cone

  DEALLOCATE cone

  SET @strsql2 = 'SELECT spname, sptext

                    FROM #final

                   WHERE sptext like ''%' + RTRIM(@search_string) + '%''

                ORDER BY spname'

  EXECUTE(@strsql2)

  END

   

  GO