SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Searching stored procedure for table name but ignoring comments


Searching stored procedure for table name but ignoring comments

Author
Message
Gopakumar-375323
Gopakumar-375323
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 36
I have used this. This will help us to ignore the commented lines as well as the line prior to the CREATE statetments. Look like conventional but helpful


--Exec dbo.search 'test'
Create Procedure [dbo].[search] (@searchstr varchar(100))
As
Declare @cntr int
Declare @cnt int
Declare @proc_name varchar(100)
Declare @proc_type varchar(100)
Declare @seq int
Declare @out_str varchar(255)
Create Table #proc_names (sl_no int identity(1,1), proc_name varchar(100),type varchar(100))
Insert into #proc_names
Select name,Case xtype When 'P' Then 'Stored Procedure'
When 'V' Then 'View'
When 'TR' Then 'Trigger'
When 'FN' Then 'Function' End
from dbo.sysobjects where xtype in ('P','V','TR','FN') and uid = 1

Create Table #proc_script (seq_no int identity(1,1), proc_str text)
Create Table #Rtn_proc_str (sl_no int identity(1,1), object_name varchar(100), object_type varchar(100), errortext varchar(255))

Set @cntr = 1
Select @cnt = count(1) from #proc_names

While @cntr <> @cnt
Begin
Select @proc_name = proc_name, @proc_type = type from #proc_names where sl_no = @cntr
BEGIN TRY
Insert into #proc_script
Exec dbo.sp_helptext @proc_name
END TRY
BEGIN CATCH
Set @out_str = ERROR_MESSAGE()
Insert into #Rtn_proc_str
Values (@proc_name, @proc_type, @out_str)
END CATCH

Select @seq = seq_no from #proc_script where proc_str like 'Create %'
Delete from #proc_script where seq_no < @seq
Delete from #proc_script where proc_str like '--%'

If exists (Select 1 from #proc_script where proc_str like '%'+@searchstr+'%')
Begin
Insert into #Rtn_proc_str
Values (@proc_name, @proc_type,'')
End
Truncate Table #proc_script
Set @cntr = @cntr + 1
End
Select object_name, object_type, errortext from #Rtn_proc_str order by object_type, object_name




Felipe Maurer
Felipe Maurer
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 Visits: 85
The script is great!!!

Do you happen to have one that does the exact opposite? I'm doing Peer Review of a DB project and need to find all SP without a comments section.

Thanks
brian.battles
brian.battles
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 35
Hi, Lowell!

How do you actually call your code snippet that ignores text inside comments?

--Brian
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74889 Visits: 40985
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%'



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
brian.battles
brian.battles
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 35
Cool, I'll try playing with that.

What I'm trying to do is create a Stored Procedure that will take a string of text as a parameter, and then search all the database objects, in all schemas, within a given database, and return a table showing all the objects the string appears, or optionally, to do the same but ignore any object where the search string only appears in the comments. And I think I need to create it in the master database to make it available in all the other databases on my server.
Lowell
Lowell
SSC Guru
SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)SSC Guru (74K reputation)

Group: General Forum Members
Points: 74889 Visits: 40985
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Davio
Davio
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 1250
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

,



Attachments
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search