brian wow it's been a while since i saw this thread.

Today, I actually do the same with regular expressions and a CLR; if that's an option, let me know and i'll pso tthat too.

here's a little more advanced snippet , all via TSQL, which creates a temp table of all definitions, and then strips out the comments, based on my earlier post.

in theory, the finished temp table has just the object definitions, so if you search THAT, you get just the items that truly reference the string you search for:

IF (SELECT OBJECT_ID('Tempdb.dbo.#tmp')) IS NOT NULL

DROP TABLE #tmp

SELECT

Schema_Name(objz.schema_id) As SchemaName,

objz.name As ObjectName,

objz.type As TypeCode,

objz.type_desc As ObjectType,

modz.definition

into #tmp

FROM sys.objects objz

INNER JOIN sys.sql_modules modz

on objz.object_id = modz.object_id

--################################################################################################

--Pre Step

--generic cleanup:

--some definitions may end in only vbLf / CHAR(10), and not my convention of vbCrLf/CHAR(13) + CHAR(10)

--this cleanup is REQUIRED because we need some sort common of End-Of-Line indicator for single line comments.

UPDATE #tmp

SET [definition] = REPLACE([definition],CHAR(10),CHAR(13) + CHAR(10))

UPDATE #tmp

SET [definition] = REPLACE([definition],CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10))

--################################################################################################

--'objective: strip out comments.

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

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

WHILE EXISTS(SELECT 1 FROM #tmp WHERE CHARINDEX('/*',[definition]) > 0 )

BEGIN

UPDATE #tmp

SET [definition] = STUFF([definition],

CHARINDEX('/*',[definition]),

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

'')

WHERE CHARINDEX('/*',[definition]) > 0

IF @@ROWCOUNT = 0

BREAK;

END --WHILE

--################################################################################################

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

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

WHILE EXISTS(SELECT 1 FROM #tmp

WHERE CHARINDEX('--',[definition]) > 0

AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0 )

BEGIN

UPDATE #tmp

SET [definition] = STUFF([definition],

CHARINDEX('--',[definition]),

CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) - CHARINDEX('--',[definition]) + 2,

'')

WHERE CHARINDEX('--',[definition]) > 0

AND CHARINDEX(CHAR(13) + CHAR(10),[definition],CHARINDEX('--',[definition])) > 0

IF @@ROWCOUNT = 0

BREAK;

END --WHILE

SELECT * FROM #tmp

--WHERE definition LIKE 'MyObjectName%'