Remove SQL comments

11,173

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.

Share:
11,173
Hiren Gondaliya
Author by

Hiren Gondaliya

Updated on June 04, 2022

Comments

  • Hiren Gondaliya
    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?