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
@StringtoFindVARCHAR(100)= NULL,
@DisplayFoundInCodeOnlybit = 0,
@DisplayFoundInCommentOnlybit = 0,
@displaydetailbit = 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
,