Remove Comments from SQL Script

Category:

Database

Type:

Snippets

Difficulty:

Beginning

Author:

Ebi Yesunesadhas

Version Compatibility: Visual Basic 6

More information:

Routine user to Strip out comments from SQL Server Script( Stored Procedure Script)

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
Public fso As New FileSystemObject

Code:

Private Function fnRemoveComment(ByVal sSrc As String _
                               , ByVal sDes As String _
                                ) As Boolean
   On Error GoTo ErrorHandler
   Dim bDel As Boolean, i As Integer
   Dim oSrc As TextStream, oDes As TextStream, s As String, _
       sLine As String, t As String
   Dim iBComment As Integer, bLComment As Boolean
   bDel = False
   If sSrc = sDes Then
       sSrc = fso.GetSpecialFolder(2) & "\Temp.SQL"
       If fso.FileExists(sSrc) Then fso.DeleteFile sSrc, True
       fso.MoveFile sDes, sSrc
       bDel = True
   End If
   If Not fso.FileExists(sSrc) Then GoTo DisposeAndGo
   Set oSrc = fso.OpenTextFile(sSrc, ForReading, False)
   Set oDes = fso.CreateTextFile(sDes, True)
   iBComment = 0
   Do While Not oSrc.AtEndOfStream
     sLine = RTrim(oSrc.ReadLine)
     Do While Right(sLine, 1) = vbTab
          sLine = Left(sLine, Len(sLine) - 1)
     Loop
     If Trim$(sLine) = "" Then GoTo Nextline
      For i = 1 To Len(sLine)
        s = Mid$(sLine, i, 2)
        If Len(s) = 2 Then
         Select Case s
            Case "*/"
                   iBComment = iBComment - 1
            Case "/*"
                  iBComment = iBComment + 1
            Case "--"
                If iBComment = 0 Then GoTo Nextline
          End Select
        End If
        If iBComment = 0 And s <> "*/" And Len(s) = 2 Then
                s = Mid$(sLine, i, 1)
                oDes.Write s
        End If
    Next i
    t = Right(sLine, 1)
    If iBComment = 0 And Right(sLine, 2) <> "*/" Then
              oDes.Write t
    End If
    If iBComment = 0 Then oDes.Write vbNewLine
Nextline:
   Loop
   oDes.Close
   oSrc.Close
   If bDel Then fso.DeleteFile sSrc, True
DisposeAndGo:
   Set oSrc = Nothing
   Set oDes = Nothing
   Exit Function
ErrorHandler:
   MsgBox "Error in RemoveComments :" & Err.Description
   Set oSrc = Nothing
   Set oDes = Nothing
   fnRemoveComment = False
End Function