December 31, 2011 at 1:52 am
i want to fiddle with procedure definitions so i can compare different versions.
my objective is to strip out comments, then strip out extra whitespace, so i can compare procedures to see if they are identical codewise, but not textwise.
for stripping comments, i've done this already in a few forum posts here, but i did it with loops;, now, I want to avoid creating a function and do it all via code.
I'd like to do it with a recursive CTE instead, but it's 3:45 in the morning, and i cannot seem to get my arms around a recursive CTE to replace a while loop.
if someone can throw me any example of a recursive CTE replacing a while loop, I'm sure i can build it myself, but I'm drawing a blank, and my bank of snippets for recursive cte's seem to be related to hierarchies....not what i need in that case.
here's the snippet i used before to strip comments:it's so SQL 2000 like style, it's just got to be replaced.
CREATE FUNCTION StripComments(@definition varchar(max))
RETURNS varchar(max)
AS
BEGIN
declare @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)
--'objective: strip out comments.
--first loop is going to look for sets of '--' and vbCrLf and STUFF them with empty space.
--second loop is going to look for pairs of '/*' and '*/', and STUFF them with empty space.
--===== 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,
'')
--===== 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
'')
return @definition --you can now search this without false positives from comments.
END
GO
select
OBJECT_NAME(object_id) As ObName,
dbo.StripComments(definition) As TheDef
from sys.sql_modules
)
Lowell
January 1, 2012 at 6:12 am
OK I'm thinking a *little* straighter on how to do it, but i'm not that close.
i can use a Tally table method to get the comments from the definition of a procedure.
i couldn't think of how to do the opposite...get all the text except the comments;
that left me with with trying our a massive replace, but even that's not running right...it's SLOW, and returns a row for each comment that was stripped.
here's what i've got so far, where i'm stripping out just single line comments, and only for the couple of procs whiuch happens to exist, since it's so slow.
;WITH MyCTE AS
(
SELECT TOP 2
objz.object_id as [ProcedureID],
objz.name as [ProcedureName],
objz.schema_id AS [SchemaID],
schema_name(objz.schema_id) as [SchemaName],
REPLACE(REPLACE(MODS.[definition],CHAR(10),CHAR(13) + CHAR(10)),CHAR(13) + CHAR(13) + CHAR(10),CHAR(13) + CHAR(10)) AS definition
--INTO AllProcedures
FROM sys.sql_modules MODS
INNER JOIN sys.objects objz
ON objz.object_id = MODS.object_id
WHERE objz.type_desc IN('SQL_STORED_PROCEDURE')
and objz.is_ms_shipped = 0
ORDER BY create_date desc
),
LineComments
AS
(
SELECT
T2.[ProcedureID],
T2.[ProcedureName],
T2.[SchemaID],
T2.[SchemaName],
T1.N,
SUBSTRING( T2.[definition],
( CASE T1.N WHEN 1 THEN 1 ELSE T1.N + len('--') END ),
( CASE CHARINDEX( CHAR(13) + CHAR(10), T2.[definition], T1.N + len('--') )WHEN 0 THEN LEN( T2.[definition] ) - T1.N + len('--')
ELSE CHARINDEX( CHAR(13) + CHAR(10), T2.[definition], T1.N + len('--') ) - ( T1.N + len('--') ) END ) ) AS DesiredString ,
[definition]
FROMTally T1
CROSS APPLY MyCTE T2
WHEREN <= LEN( T2.[definition] )
AND SUBSTRING( T2.[definition], T1.N, len('--') ) = '--'
)
SELECT REPLACE(MyCTE.[definition],'--' + DesiredString + CHAR(13) + CHAR(10),'') as def
FROM MyCTE LEFT OUTER JOIN LineComments ON MyCTE.[ProcedureID] = LineComments.[ProcedureID]
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply