Searching Text in many stored procedures ?

  • I have a lot of Stored Procs, I wish to run thru the code in each one and find where I have used certain words ? Say Find "TRUNCATE", any ideas how to do this ? Or is there code about for this ?

  • one of these scripts should be able to help you out:

    sp to search string

    search for text

    search all user objects

    search for specific string







    **ASCII stupid question, get a stupid ANSI !!!**

  • I've used this for a couplr of years and it seems to work pretty well:

    -- search for string in stored procedures in DB

    --

    -- note: the reason this joins to a second instance of syscomments is to find cases where

    --the string being searched for 'spans' two rows in syscomments. (the text of the stored

    --procedure is whacked into 'chunks' and stored in the .text column of multiple rows in

    --syscomments)

    select distinct so.name from  sysobjects so

     join syscomments sc on sc.id = so.id

     left outer join  syscomments sc1 on  sc1.id = sc.id and  sc1.colid = sc.colid + 1

      where  so.type = 'p'

        and  ( sc.text like '%string%'

         or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%'

       )

      order by so.name

    Greg

    Greg

  • Thx for Help EXCELLENT forum

    Big Cheers

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

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