Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Searching stored procedure for table name but ignoring comments Expand / Collapse
Author
Message
Posted Thursday, February 9, 2012 7:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 11:58 AM
Points: 1, Visits: 16
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



Post #1249719
Posted Monday, September 3, 2012 6:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 7, 2013 4:42 PM
Points: 10, Visits: 45
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
Post #1353653
Posted Monday, April 8, 2013 7:36 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:53 AM
Points: 9, Visits: 35
Hi, Lowell!

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

--Brian
Post #1439826
Posted Monday, April 8, 2013 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439854
Posted Monday, April 8, 2013 8:18 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 5:53 AM
Points: 9, 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.
Post #1439859
Posted Monday, April 8, 2013 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:53 AM
Points: 12,890, Visits: 31,851
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1439886
Posted Tuesday, May 28, 2013 4:28 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 85, Visits: 876
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

,





  Post Attachments 
SearchforTargetInsideOrOutsideComments.rar (2 views, 3.19 KB)
Post #1457543
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse