per Brians request, here's a parameterized stored proc using the logic i posted above;

there's some cleanup that should be introduced where im arbitrarily doing a REPLACE of vbLf with vbCrLf, but this seemed to wortk in a limited testing i did on my dev machine:

--note: to use this in any database, it must start with sp_

--and also must be registered as a system object:

--EXECUTE sp_ms_marksystemobject 'sp_SearchDefinitions'

--DROP PROCEDURE sp_SearchDefinitions

--usage:

--by default, this will ignore comments in the stored procedure unless teh @ignoreComments is passed as zero

--this proc is painfully slow because of the amount of manipulation required

--usage:

--EXEC sp_SearchDefinitions VW_BUDGETS_AWARD

--EXEC sp_SearchDefinitions tblOrganizations,0 --include comments

CREATE PROCEDURE sp_SearchDefinitions(@SearchTerm varchar(255),@ignoreComments int = 1)

AS

BEGIN

SET NOCOUNT ON

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

IF @ignoreComments != 0

BEGIN

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

--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

END --IF

--get the results

SELECT *

FROM #tmp

WHERE definition LIKE '%' + @SearchTerm + '%'

END --PROC