September 16, 2009 at 6:29 pm
Anyone have a script that will not only find instances of a string in stored procedures on your db (easy part) but also will ignore it if there it only finds it in the commented out code?
Example: Show me all procs that have the word "employee" in it. Exclude if the word employee only occurs as part of a comment in the code.
Remember this when a developer tells you it will just be temporary. Temporary = Permanent.
September 16, 2009 at 9:38 pm
Hi,
There is no document and undocument procedures available to direct get this result,
you should work lot of scquecne to arrive this result,
Do first with to identify of the result procedure by
SELECT * FROM syscomments
where text like '%employee%'
September 23, 2009 at 2:54 pm
I have had the same problem. There is nothing out there that does a fair job of removing comments. This is my solution which is part of a larger solution to verify standards against code. I think I have managed to remove anything not related to your problem and left working code. Let me know if you have any problems
Chuck
use master
go
/*==========================================================================================
Author:Lucking, Chuck
Phoenix Police Dept
Create date:08/05/2009
Description:
Given one of the following as inputs
'ObjectName', '0'(Parses a given object)
'\\server\share\File2parse.sql','F'(parses a given file)
null, 'D'(parse the complete database)
Return global temp files which can be further queried
##CommentText (Unmodified text of each object)
##NoComments (Text with all comments removed and each word padded with a space. ex. 'varchar(100)' becomes ' varchar ( 100 ) '
This is a watered down version of sp_ParseComments for sharing purposes only
==========================================================================================*/
alter PROC [dbo].[sp_ParseOutComments]
@chvDef VARCHAR(100)
, @chrType CHAR(1) = 'O'
, @bitDebug BIT = 0
AS
SET nocount ON
SET quoted_identifier OFF
DECLARE @chvVarTypeText VARCHAR(1000)
, @chvCurrentUser VARCHAR(100)
, @chvParseStatus VARCHAR(2)
, @nchvDbName SYSNAME
, @intDeclare INT
, @chvComment VARCHAR(600)
, @chvCommentNC VARCHAR(555)
, @intOffset INT
, @inyChar TINYINT
, @intVarMode INT
, @intReturn INT
, @chvOType VARCHAR(30)
, @bitbypass BIT
, @intCkSum INT
, @chvSubChar VARCHAR(5)
, @chvLastSubChar VARCHAR(1)
, @intLen INT
, @intID INT
, @chvOwner VARCHAR(40)
, @intMinID INT
, @chvObjectType VARCHAR(40)
, @chvObjectName VARCHAR(128)
, @intVarObjectOn INT
, @chvSysObjName VARCHAR(128)
-- drop table ##currentuser
-- select * from ##currentuser
IF @chrType NOT IN ( 'O', 'F', 'D' )
BEGIN
PRINT "Type must be O (ObjectName) or F (filename) or D (Database) "
RETURN
END
IF OBJECT_ID('tempdb..##NoComments') > 0
DROP TABLE ##NoComments
IF OBJECT_ID('tempdb..##CommentText') > 0
DROP TABLE ##CommentText
IF OBJECT_ID('tempdb..##variables') > 0
DROP TABLE ##variables
IF OBJECT_ID('TEMPdb..##WORDS') > 0
DROP TABLE ##WORDS
CREATE TABLE ##Variables ( id INT IDENTITY
, ObjectType VARCHAR(30)
, Owner VARCHAR(40)
, ObjectName VARCHAR(128)
, VarName VARCHAR(128)
, isCompliant BIT
, VarType VARCHAR(20)
, Prefix VARCHAR(10) COLLATE Latin1_General_CS_AS
, Occurances INT NULL
, QtyMatch INT NULL )--, isCompliant BIT )
CREATE TABLE ##NoComments ( id INT IDENTITY
, Text VARCHAR(1000) )
CREATE TABLE ##CommentText ( id INT IDENTITY, Wrap bit default 0
, Text VARCHAR(1000) COLLATE database_default )
CREATE TABLE ##WORDS ( VAROBJNAME VARCHAR(30)
, WORD VARCHAR(200) )
IF @chrType = 'O'
BEGIN
IF OBJECT_ID(@chvDef) IS NULL
BEGIN
PRINT 'Object does not exist' ;
RETURN
END
SELECT @chvDef = 'sp_helptext ''' + @chvDef + ''''
INSERT ##CommentText ( [text] )
EXECUTE ( @chvDef )
END
IF @chrType = 'F'
BEGIN
SELECT @chvVarTypeText = 'type ' + @chvDef
INSERT ##CommentText ( [text] )
EXEC @intReturn = master..xp_cmdshell @chvVarTypeText
IF @intReturn = 1
BEGIN
PRINT 'File ' + @chvDef + ' does not exist'
RETURN
END
PRINT 'F option (parsing a text file) will not parse parameter variables.'
END
IF @chrType = 'D'
BEGIN
DECLARE curObjectName CURSOR
FOR SELECT DISTINCT
'[' + name + ']', Type, USER_NAME(uid) -- All objects which have comments
FROM sysobjects o
JOIN syscomments c ON o.id = c.id
WHERE OBJECTPROPERTY(o.id, 'ismsshipped') = 0
AND O.TYPE IN ( 's', 'tr', 'fn', 'P', 'if', 'tf' ) -- skip views when doing all objects
AND encrypted = 0
ORDER BY '[' + name + ']', type
OPEN curObjectName
FETCH NEXT FROM curObjectName INTO @chvSysObjName, @chvOType, @chvOwner
WHILE @@fetch_status = 0
BEGIN
SELECT @chvDef = 'sp_helptext ''' + @chvOwner + '.' + @chvSysObjName + ''''
INSERT ##CommentText ( [text] )
EXECUTE ( @chvDef )
IF @bitDebug = 1
PRINT @chvDef
INSERT ##CommentText ( [text] )
SELECT 'Go'
FETCH NEXT FROM curObjectName INTO @chvSysObjName, @chvOType, @chvOwner
END
CLOSE curObjectName
DEALLOCATE curObjectName
END
update ##CommentText set Wrap = 1 where LEN(text) = 255
IF EXISTS ( SELECT *
FROM ##COMMENTTEXT
WHERE Wrap = 1)
BEGIN
SELECT @intMinID = MIN(ID)
FROM ##COMMENTTEXT
WHERE Wrap = 1
IF @bitDebug = 1
PRINT '** Comments = 255 length ' + CONVERT (VARCHAR(10), @intMinID)
WHILE @intMinID IS NOT NULL
BEGIN
if @bitDebug = 1 select Id , 'Rows that wrap' = Text from ##CommentText where id in ( @intMinID, @intMinID + 1)
update ##commenttext set text = TEXT + (select TEXT from ##commentText where ID = @intMinID + 1) , Wrap = 0 where ID = @intMinID
if @bitDebug = 1 select ID, 'Fix of wrap rows' = TEXT from ##CommentText where id = @intMinID
delete ##commenttext where ID = @intMinID + 1
select @intMinID = MIN(id) from ##commenttext where wrap = 1 and ID > @intMinID
end
END
DECLARE curComment CURSOR
FOR SELECT RTRIM(CONVERT (VARCHAR(1000), text)), ID
FROM ##Commenttext
ORDER BY ID
OPEN curComment
SELECT @chvCommentNC = '', @chvParseStatus = '', @bitbypass = 0, @intCkSum = CHECKSUM(@chvObjectName, @chvObjectType)
-- Init variables
SELECT @intOffset = 0, @intDeclare = 0, @intVarMode = 1, @intVarObjectOn = 0, @chvLastSubChar = ''
FETCH NEXT FROM curComment INTO @chvComment, @intID
WHILE @@fetch_status = 0
BEGIN
SELECT @chvComment = REPLACE(@chvComment, CHAR(10), '') -- Get rid of any line feeds
SELECT @chvComment = ' ' + @chvComment
IF @bitDebug = 1
PRINT '---> ' + @chvComment
SELECT @chvCommentNC = '', @chvParseStatus = CASE @chvParseStatus
WHEN 'DD' THEN ''
ELSE @chvParseStatus
END
SELECT @intLen = LEN(@chvComment) + 1
WHILE @intOffset < @intLen
BEGIN
SELECT @chvSubChar = SUBSTRING(@chvComment, @intOffset, 1)
SELECT @inyChar = ASCII(@chvSubChar)
-- Use following two lines of code to remove all formating, from ##NoComments if desired
-- IF @chvParseStatus = '' AND @chvSubChar = CHAR(9) SELECT @chvSubChar = ' '-- Tabs become spaces
-- IF @chvParseStatus = '' AND @chvSubChar = ' ' AND @chvLastSubChar = ' ' SET @chvSubChar = '' -- Eliminate excess white space
IF SUBSTRING(@chvComment, @intOffset, 2) = '--'
AND @chvParseStatus = ''
SELECT @chvParseStatus = 'DD'
IF SUBSTRING(@chvComment, @intOffset, 2) = '/*'
AND @chvParseStatus = ''
BEGIN
SELECT @chvParseStatus = 'BC'
SELECT @intOffset = @intOffset + 1
END
IF SUBSTRING(@chvComment, @intOffset, 2) = '*/'
AND @chvParseStatus = 'BC'
BEGIN
SELECT @chvParseStatus = ''
SELECT @chvComment = STUFF(@chvComment, @intOffset, 2, ' ')
SET @chvSubChar = ''
END
IF @inyChar = 39 -- '
SELECT @chvParseStatus = CASE @chvParseStatus
WHEN '' THEN 'SQ'
WHEN 'SQ' THEN ''
ELSE @chvParseStatus
END
IF @inyChar = 34 -- "
SELECT @chvParseStatus = CASE @chvParseStatus
WHEN '' THEN 'DQ'
WHEN 'DQ' THEN ''
ELSE @chvParseStatus
END
IF @chvParseStatus = ''
BEGIN
IF @chvSubChar IN ( '=', '(', ')', '', ',', '*' )
AND @chvLastSubChar = ' '
SET @chvSubChar = @chvSubChar + ' '
IF @chvSubChar IN ( '=', '(', ')', '', ',', '*' )
AND @chvLastSubChar ' '
SELECT @chvSubChar = ' ' + @chvSubChar + ' ', @chvLastSubChar = ''
IF @chvSubChar = CHAR(13) -- add space to end of each row
SELECT @chvSubChar = ' ' + @chvSubChar
END
IF @chvParseStatus IN ( 'SQ', 'DQ' )
SELECT @chvCommentNC = @chvCommentNC + @chvSubChar
IF @chvParseStatus = ''
SELECT @chvCommentNC = @chvCommentNC + @chvSubChar
SELECT @intOffset = @intOffset + 1
SELECT @chvLastSubChar = RIGHT(@chvSubChar, 1)
END
SET @intOffset = 1
FETCH NEXT FROM curComment INTO @chvComment, @intID
BEGIN -- New LIne
IF LEN(@chvCommentNC) > 0
INSERT ##NOComments
SELECT @chvCommentNC --
IF @bitDebug = 1
AND LEN(@chvCommentNC) > 0
PRINT ' ' + @chvCommentNC --
IF @chvParseStatus = 'DD'
SET @chvParseStatus = ''
SET @chvLastSubChar = ''
END
END
CLOSE curComment
DEALLOCATE curComment
go
September 24, 2009 at 7:21 am
Chuck's idea got me thinking....his requirement obviously was more expansive, but i justed tested this solution below, and it strips out all the comments; so if you use the results of this process to search, you'd automatically ignore anything in the comments.
try this, simnply change the SET @objectname = 'sp_getDDL' to a procedure you have that has comment of both /* */ and -- types:
declare @definition varchar(max),
@objectname varchar(255),
@vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @objectname = 'sp_getDDL'
select @definition = definition + 'GO' + @vbcrlf from sys.sql_modules where [object_id] = object_id(@objectname)
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE CHARINDEX('/*',@definition) > 0
SELECT @definition = STUFF(@definition,
CHARINDEX('/*',@definition),
CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term
'')
--===== Replace all single line comments
WHILE CHARINDEX('--',@definition) > 0
AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)
SELECT @definition = STUFF(@definition,
CHARINDEX('--',@definition),
CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,
'')
print @definition --you can now search this without false positives from comments.
Lowell
September 24, 2009 at 7:35 am
just noticed that you posted in a 2000 forum: the solutions is still the same, just a different table to get the data, plus you will be handicapped because syscomments uses 4000 char slices...any proc that is greater than 4000 chars, this grabs the last group of bytes... 4001 thru 8000, for example:
declare @definition varchar(8000),
@objectname varchar(255),
@vbCrLf CHAR(2),
@Start int,
@End int
SET @vbCrLf = CHAR(13) + CHAR(10)
SET @objectname = 'sp_getDDL'
select @definition = text + 'GO' + @vbcrlf from syscomments where [id] = object_id(@objectname)
--'objective: strip out comments.
--first loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--second loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--===== Replace all '/*' and '*/' pairs with nothing
WHILE CHARINDEX('/*',@definition) > 0
SELECT @definition = STUFF(@definition,
CHARINDEX('/*',@definition),
CHARINDEX('*/',@definition) - CHARINDEX('/*',@definition) + 2, --2 is the length of the search term /*
'')
--===== Replace all single line comments
WHILE CHARINDEX('--',@definition) > 0
AND CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) > CHARINDEX('--',@definition)
SELECT @definition = STUFF(@definition,
CHARINDEX('--',@definition),
CHARINDEX(@vbCrLf,@definition,CHARINDEX('--',@definition)) - CHARINDEX('--',@definition) + 2,
'')
print @definition
Lowell
September 27, 2009 at 6:59 pm
I just want to add some more stuff:
1) For SQL Server 2005 you should use sys.sql_modules. It returns just one row for each SP (not like syscomments), which makes the search much easier and less error prone
2) Although sometimes helpful, be aware this search code has some issues:
a) in fact, multi-line comment does not have precedence over single-line one. The first comment takes precedence, but the code above first removes multi-line, and then - single-line comments.
b) it does not check if '/*' or '--' is taken into quotes (that means that this is not comment at all, but just a varchar string.
September 28, 2009 at 4:24 pm
One bit to bear in mind is correct lexical state, for example
-- /*
Print 'hello'
-- */
prints hello, even though it is between two '/*' and '*/' markers. Might be worth replacing the single line comments first? Obviously not a pleasant syntax use, but an edge case 🙂
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
March 29, 2017 at 2:51 pm
I needed a similar solution. I liked Lowell's clever use of the STUFF function (thanks). The problem I had, as mentioned above, is that multi and single line comments can both override each other.
-- /* multi-line ignored
/*
--*/ this text is not a comment
My solution was to parse through the file one by one. I kept it as simple as possible. It handles fairly complex nested comments, without a terrible amount of looping and variables.
CREATE FUNCTION dbo.RemoveComments (@Value VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
SET @value = REPLACE(@value,'/*/*','/* /*')
DECLARE @cursor INT = 0 ;
WHILE PATINDEX('%--%', @Value) > 0 OR PATINDEX('%/*%', @Value) > 0
BEGIN
IF CHARINDEX('--', @Value, 0)BETWEEN 1 AND CHARINDEX('/*', @Value, 0)
OR CHARINDEX('/*', @Value, 0) = 0
BEGIN
SET @Value = STUFF(@Value, CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX( CHAR(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 1, '') ;
END ;
ELSE
BEGIN
SET @cursor = -1 ;
WHILE CHARINDEX('/*', @Value, @cursor + 1)BETWEEN 1 AND CHARINDEX('*/', @Value, @cursor + 1)
SET @cursor = CHARINDEX('/*', @Value, @cursor + 1) ;
SET @Value = STUFF(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2, '') ;
END ;
END ;
RETURN @Value ;
END ;
go
----------------------------------------------------
select dbo.removecomments( '--test
/*/*/*/*/*/*
remove1
*/*/*/*/*/*/
--remove2 /* remove3
keep1--*/ remove2
/* remove4*//* -- remove5 */ keep2
--/*
keep3
-- remove6
--*/
keep4
--/*
keep5/*
--/*
remove7
--*/
*/keep6--*/
/* */ keep7
/*--remove8 /* remove 9 */ remove 10 */ keep8 --remove11' )
March 29, 2017 at 6:11 pm
Spinja - Wednesday, March 29, 2017 2:51 PMI needed a similar solution. I liked Lowell's clever use of the STUFF function (thanks). The problem I had, as mentioned above, is that multi and single line comments can both override each other.-- /* multi-line ignored
/*
--*/ this text is not a commentMy solution was to parse through the file one by one. I kept it as simple as possible. It handles fairly complex nested comments, without a terrible amount of looping and variables.
CREATE FUNCTION dbo.RemoveComments (@Value VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGINDECLARE @cursor INT = 0 ;
WHILE PATINDEX('%--%', @Value) > 0 OR PATINDEX('%/*%', @Value) > 0
BEGIN
IF CHARINDEX('--', @Value, 0)BETWEEN 1 AND CHARINDEX('/*', @Value, 0)
OR CHARINDEX('/*', @Value, 0) = 0
BEGIN
SET @Value = STUFF(@Value, CHARINDEX('--', @Value), ISNULL(NULLIF(CHARINDEX(CHAR(13) + CHAR(10), @Value, CHARINDEX('--', @Value)), 0), LEN(@Value)) - CHARINDEX('--', @Value) + 2, '') ;
END ;ELSE
BEGIN
SET @cursor = -1 ;
WHILE CHARINDEX('/*', @Value, @cursor + 1)BETWEEN 1 AND CHARINDEX('*/', @Value, @cursor + 1)
SET @cursor = CHARINDEX('/*', @Value, @cursor + 1) ;SET @Value = STUFF(@Value, @cursor, CHARINDEX('*/', @Value, @cursor) - @cursor + 2, '') ;
END ;
END ;RETURN @Value ;
END ;
go
----------------------------------------------------select dbo.removecomments( '
/*
/*
/*
/*
remove1
*/*/*/*/
--remove2 /* remove3
keep1--*/ remove4
/* remove5*//* -- remove6 */ keep2
--/*
keep3
-- remove7
--/*
keep4/*
--/*
remove8
--*/
*/keep5--*/
' )
Welcome to SSC. You could submit what you posted as a script here and help more people out. 😉
-- Itzik Ben-Gan 2001
April 2, 2017 at 6:11 pm
Heh... and then there's string literals, which may also contain things like the word "Employee", which could be the name of a table.,
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2024 at 2:40 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply