Lowell (11/28/2010)

---

I have this snippets saved which might help; it's using a pair of loops table to strip out any comments , so you can then search just the remaining proc definition;

declare @definition varchar(max),

@objectname varchar(255),

@vbCrLf CHAR(2)

SET @vbCrLf = CHAR(13) + CHAR(10)

SET @objectname = 'sp_getDDL'

select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)

--'objective: strip out comments.

--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.

--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.

--===== Replace all '/*' and '*/' pairs with nothing

WHILE CHARINDEX('/*',@definition) > 0

SELECT @definition = STUFF(@definition,

CHARINDEX('/*',@definition),

CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term

'')

--===== Replace all single line comments

WHILE CHARINDEX('--',@definition) > 0

AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)

SELECT @definition = STUFF(@definition,

CHARINDEX('--',@definition),

CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,

'')

print @definition --you can now search this without false positives from comments.