store procedure

  • 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

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

  • 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

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

    http://sqldigger.bdsweb.be/

  • 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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply