Remove SQL comments
Solution 1
I use an SQL tool called WinSQL (very handy, highly reccommended) that has an option to "Parse Comments Locally".
I don't use it much personally, but I have had it on accidentally when running my scripts that build my stored procs and it does clean them out of the proc source in the database. :-)
Even the free version has that option.
Solution 2
Don't know if it would suit, but you can use the WITH ENCRYPTION option to hide the entire contents. Do your end users need to see/modify any of the procedures?
Solution 3
This option wasn't available when the question was asked, but in SQL 2012, we can now use SQL Server's own parser to help us out. Removing Comments From SQL
Solution 4
A little late to the party but in case someone else stumbles across...
CREATE FUNCTION [usf_StripSQLComments] ( @CommentedSQLCode VARCHAR(max) )
RETURNS Varchar(max)
/****************************************************************************************
--######################################################################################
-- [email protected] -- Some count sheep. Some code. Some write code to count sheep.
--######################################################################################
--#############################################################################
-- Sample Call Script
--#############################################################################
Declare @SqlCode Varchar(Max)
Declare @objname varchar(max) = 'sp_myproc'
select @Sqlcode = OBJECT_DEFINITION(t.OBJECT_ID)
from sys.objects t
where t.name = @objname
select dbo.ssf_StripSQLComments( @Sqlcode )
****************************************************************************************/
AS
BEGIN
DECLARE @Sqlcode VARCHAR(MAX) =@CommentedSQLCode
Declare @i integer = 0
Declare @Char1 Char(1)
Declare @Char2 Char(1)
Declare @TrailingComment Char(1) = 'N'
Declare @UncommentedSQLCode varchar(Max)=''
Declare @Whackcounter Integer = 0
Declare @max Integer = DATALENGTH(@sqlcode)
While @i < @max
Begin
Select @Char1 = Substring(@Sqlcode,@i,1)
if @Char1 not in ('-', '/','''','*')
begin
if @Char1 = CHAR(13) or @Char1 = CHAR(10)
Select @TrailingComment = 'N'
Else if not (@Char1 = CHAR(32) or @Char1 = CHAR(9)) and @TrailingComment = 'N' -- Not Space or Tab
Select @TrailingComment = 'Y'
if @Whackcounter = 0
Select @UncommentedSQLCode += @Char1
select @i+=1
end
else
begin
Select @Char2 = @Char1
, @Char1 = Substring(@Sqlcode,@i+1,1)
If @Char1 = '-' and @Char2 = '-' and @Whackcounter = 0
Begin
While @i < @Max and Substring(@Sqlcode,@i,1) not in (char(13), char(10))
Select @i+=1
if Substring(@Sqlcode,@i,1) = char(13) and @TrailingComment = 'N'
Select @i+=1
if Substring(@Sqlcode,@i,1) = char(10) and @TrailingComment = 'N'
Select @i+=1
End
else If @Char1 = '*' and @Char2 = '/'
Begin
Select @Whackcounter += 1
, @i += 2
End
else If @Char1 = '/' and @Char2 = '*'
Begin
Select @Whackcounter -= 1
, @i += 2
End
else if @char2 = '''' and @Whackcounter = 0
begin
Select @UncommentedSQLCode += @char2
while Substring(@Sqlcode,@i,1) <> ''''
Begin
Select @UncommentedSQLCode += Substring(@Sqlcode,@i,1)
, @i +=1
end
Select @i +=1
, @Char1 = Substring(@Sqlcode,@i,1)
end
else
Begin
if @Whackcounter = 0
Select @UncommentedSQLCode += @Char2
Select @i+=1
end
end
End
Return @UncommentedSQLCode
END
Solution 5
You may want to check this out:
Remove Comments from SQL Server Stored Procedures.
Note: this doesn't handle comments that start with --, which SQL Server allows. Otherwise I would inquire into having a developer write a short filter app that reads the text in via a stream, and then remove the comments that way. Or write it yourself.
Hiren Gondaliya
Updated on June 04, 2022Comments
-
Hiren Gondaliya almost 2 years
our stored procedures have developer comments and headers and as part of our deployment process we would like to remove these from the customer copy. Is there a method of achieving this within SQL Server 2005 or with another tool?