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
Lowell