Remove SQL comments

Question

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?

Answer 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.

Answer 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?

Answer 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

Answer 4

A little late to the party but in case someone else stumbles across...

CREATE FUNCTION [usf_StripSQLComments] ( @CommentedSQLCode VARCHAR(max) )
RETURNS Varchar(max)
/****************************************************************************************
--######################################################################################
-- Mjwheele@yahoo.com  -- 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

Answer 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.

Answer 6

I ended up writing my own SQL comment remover in C#

Answer 7

I assume you save your procedure definitions to a text or .sql file that you then version control. You could always use something like notepadd++ to find/replace the strings you want then commit them as a production/customer tag. This is not elegant, but an option. I don't know of any third party tools and my google searches returned the same result as the other posters posted.

Answer 8

You can remove comments using regular expressions in C# like described here. It works for line comments, block comments, even when block comments are nested, and it can correctly identify and ignore comments delimiters when they are inside literals or bracketed named identifiers.

Answer 9

This a VB.NET code for removing SQL Coments It's supposed the script is well formated syntaxicly under SQL Management Studio

   Module Module1
        Const TagBeginMultiComent = "/*"
        Const TagEndMultiComent = "*/"
        Const TagMonoComent = "--"
    
        Public Fail As Integer
        Function IsQuoteOpened(ByVal Value As String) As Boolean
            Dim V As String = Replace(Value, "'", "")
            If V Is Nothing Then Return 0
    
            Return ((Value.Length - V.Length) / "'".Length) Mod 2 > 0
        End Function
        Function RemoveComents(ByVal Value As String) As String
            Dim RetVal As String = ""
            Dim Block As String
            Dim Tampon As String
            Dim NbComentIncluded As Integer = 0
            Dim QuoteOpened As Boolean
            Dim CommentOpen As Boolean
    
            While Value.Length > 0
                Tampon = ""
                Block = ""
                Dim P1 As Integer = InStr(Value, TagBeginMultiComent)
                Dim P2 As Integer = InStr(Value, TagEndMultiComent)
                Dim P3 As Integer = InStr(Value, TagMonoComent)
                Dim Min As Integer
    
                If P1 = 0 Then P1 = Value.Length + 1
                If P2 = 0 Then P2 = Value.Length + 1
                If P3 = 0 Then P3 = Value.Length + 1
                Tampon = ""
                If P1 + P2 + P3 > 0 Then
                    Min = Math.Min(P1, Math.Min(P2, P3))
                    Tampon = Left(Value, Min - 1)
                    Block = Mid(Value, Min, 2)
                    Value = Mid(Value, Min)
                End If
    
                If NbComentIncluded = 0 Then QuoteOpened = IsQuoteOpened(RetVal & Tampon)
    
                If Not QuoteOpened Then
                    NbComentIncluded += -(Block = TagBeginMultiComent) + (Block = TagEndMultiComent)
                    If Block = TagMonoComent Then
                        Dim Ploc As Integer = InStr(Value, vbCrLf)
                        If Ploc = 0 Then
                            Value = ""
                        Else
                            Value = Mid(Value, Ploc - 2)
                        End If
                    End If
                End If
    
                If Not CommentOpen And NbComentIncluded = 0 Then
                    RetVal += Tampon
                    If ({TagBeginMultiComent, TagEndMultiComent, TagMonoComent}.Contains(Block) And QuoteOpened) Or
                       (Not {TagBeginMultiComent, TagEndMultiComent, TagMonoComent}.Contains(Block) And Not QuoteOpened) Then RetVal += Block
                End If
                CommentOpen = (NbComentIncluded > 0)
    
                Value = Mid(Value, 3)
            End While
    
            Fail = -1 * (IsQuoteOpened(RetVal)) - 2 * (NbComentIncluded > 0)
            If Fail <> 0 Then RetVal = ""
    
            Return RetVal
        End Function
        Sub Main()
            Dim InputFileName = "C:\Users\godef\OneDrive - sacd.fr\DEV\DelComentsSql\test.txt" '"C:\Users\sapgy01\OneDrive - sacd.fr\DEV\DelComentsSql\test.txt"
            Dim Script As String = File.ReadAllText(InputFileName)
            Dim InputDataArray As String() = Split(Script, vbCrLf)
    
            Script = RemoveComents(Script)
            If Fail Then
                Console.WriteLine($"Fail : {Fail}")
                If Fail And 1 = 1 Then Console.WriteLine("Toutes les quotes ne sont pas refermées")
                If Fail And 2 = 2 Then Console.WriteLine("Tous les commentaires multiliqnes ne sont pas refermées")
            Else
                Console.WriteLine(Script)
            End If
    
            Console.ReadKey()
        End Sub
    End Module

Addon : a check is made for unclosed multilines coment and/or unclosed apostroph.

Example :

/* Commentaire principal
   Suite du commentaire principal
    /* Inclusion de commentaire
       Suite du commentaire inclu
    */ suite commentaire principal
continuation commentaire principal
    /* mono comentaire tagué multi lignes */
*/   select * from ref
    -- mono commentaire
select ref.ref_lbl_code as 'code
 de 
 la
 -- ref --
' -- from ref as 'references' -- Fin de séquence
from ref as reference -- Mono commentaire fin de ligne

 go -- lance l'exécution

select dbo.ref.REF_LBL_CODE as 'commentaire
/* Mulitlignes sur une ligne dans litteral */'
from ref as 'table_ref'

select ref.ref_lbl_code as 'commentaire
/* Mulitlignes 
sur plusieurs lignes
 dans litteral */'
from ref as '-- ref_table --'

-- Fin de l'exécution du ' -- script -- '