April 8, 2013 at 8:55 am
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
May 28, 2013 at 4:29 pm
Hi All,
I thought I would give this a try, as it is something that I did a while ago and then left in a somewhat unfinished state. I recently had some time at the end of my last contract to work on it and I think it ready to be posted. There are 4 parts: First, find all the start and end position of comments that are in comment blocks (/* .... */). Next, do the same for line comments, which have a starting position designated as "--" and an ending position of Chr(13). Then find the starting position of the text that is the search target. Finally, figure out whether the text target is inside a comment block or not inside a comment block and display the results.
The procedure is below and the 3 functions that I have borrowed and altered (from a couple of forum contributors--thanks for that) are in the zip file.
Dave
create PROCEDURE dbo.SearchforTargetInsideOrOutsideComments
@StringtoFind VARCHAR(100) = NULL,
@DisplayFoundInCodeOnly bit = 0,
@DisplayFoundInCommentOnly bit = 0,
@displaydetail bit = 0
AS
BEGIN
/***********************************************************************************************************************
This procedure searches the text of database objects and determines whether the text found is in a comment or in the code
1. find all the objects with have the text being searched
2. find all the block comment start ("/*") and end positions ("*/"_
3. find all the line comment start ("--"_ and end (char(10)) positions
4. find the start position of all the search targets
5. Determine whether search target start positions are in between comment start and end positions
6. Display search text that is in a comment, in the code or both
*************************************************************************************************************************/
declare
@List TABLE (item_type varchar(20),start int,finish int, search_text varchar(50), Name_object varchar(100), object_type varchar(50))
declare @ObjectsFound TABLE (Name_object sysname,TypeDescription nvarchar(120),object_text nvarchar(max))
declare
@sDelimiter VARCHAR(50),
@sEndDelimiter VARCHAR(50),
@sStartDelimiter VARCHAR(50),
@beginpos int,
@endpos int,
@sItem VARCHAR(8000),
@Name_object sysname,
@object_text nvarchar(max)
--put all the objects & object text found in a temp table
INSERT INTO @ObjectsFound
SELECT o.name AS Name_object,
o.type_desc AS TypeDescription,
dbo.fn_CleanUp_LineComments( REPLACE(OBJECT_DEFINITION(object_id),'''', '|')) AS object_text
FROM sys.objects o
WHERE
((o.type IN ('AF', 'FN', 'IF', 'P', 'TF', 'TT', 'U', 'V', 'X') ) )
AND OBJECT_DEFINITION(o.object_id) LIKE '%' + @StringtoFind + '%'
BEGIN
--/**/ comment block start and end
set @sStartDelimiter = '/*'
set @sEndDelimiter = '*/'
Insert into @List (Name_object,search_text,object_type,start,finish,item_type)
select Name_object,'Block Comment', TypeDescription,[start],[end], 'Block Comment' from @ObjectsFound
CROSS APPLY dbo.itvfFindPos_StartAnd_end (object_text,@sStartDelimiter,@sEndDelimiter)
END
BEGIN
--line comment start and end multiple '--' were changed to a single '~'
set @sStartDelimiter = '~'
set @sEndDelimiter = char(10)
Insert into @List (Name_object,search_text,object_type, start,finish,item_type)
SELECT
Name_object,'Line Comment',TypeDescription,[start],[end], 'Line Comment'
FROM sys.objects o1 INNER JOIN @ObjectsFound o2 on o1.name = o2.Name_object
CROSS APPLY dbo.itvfFindPos_StartAnd_end (object_text,@sStartDelimiter,@sEndDelimiter)
END
BEGIN
-- search target block start and set end to -1
Insert into @List (Name_object,search_text,object_type, start,finish,Item_type)
select Name_object,@StringtoFind, TypeDescription,pos,-1, 'Search Target' from @ObjectsFound
CROSS APPLY dbo.itvfFindPos(object_text,@StringtoFind)
END
IF @displaydetail = 1
select
item_type ,start as [Starting Position],finish as [Ending Position], search_text [Search For], Name_object [Name of DB Object], object_type [Type of DB object]
from @List order by Name_object, start
/*display the results--targets only, comments only or both*/
select distinct l1.Name_object, L1.search_text ,'Target in Code' target_status
from @List L1
where not exists (select 'x' from @List L2 where L1.Name_object = L2.Name_object and L2.search_text <>@StringtoFind
and L1.start between L2.start AND L2.finish)
and L1.search_text =@StringtoFind
and @DisplayFoundInCommentOnly = 0
UNION ALL
select distinct l1.Name_object, L1.search_text , 'Target in Comment' target_status
from @List L1
INNER JOIN @List L2 on L1.Name_object = L2.Name_object
where L1.search_text not in ('Line Comment','Block Comment')
and L2.finish > 0
and L1.start between L2.start AND L2.finish
AND @DisplayFoundInCodeOnly = 0
order by l1.Name_object
END
,
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply