Urgent:search for a key word.

  • Hi,

    I want to search for the stored procedures which are using a particular keyword.Can anyone suggest me how to write a query for that?

     

    ..hema

  • Run in the desired data base from Query Analyzer:

    declare @s varchar(255)

    set @s-2 = 'Text to Search Here'

    DECLARE @msg varchar(255) ,

      @ul varchar(255)

    select @s-2='%' + @s-2 + '%'

    select 'SP Name'=upper(o.name),

      Seq=colid ,

      'SP Line'=substring(text,patindex(@s,text)-5, 30)

    from syscomments c ,

      sysobjects o

    where o.id=c.id

    and  patindex(@s,text) > 0

    order by name

    SELECT @msg='* Stored procedures containing string "' + @s-2 + '=' +

    convert(varchar(8),@@rowcount) + ' *'

    SELECT @ul=replicate('*',datalength(@msg))

    Print ' '

    PRINT @ul

    PRINT @msg

    Print @ul

    GO

  • I want to know whether there is any

    predefined system stored procedure for finding the same and thanx for the reply...

    ..hema

  • nothing predefined, but you can easily take Tyson's example and create a stored proc witht eh same code.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

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