Michael J. Swart

Check out the following deliberately crazy SQL Script:

create table [/*] /* 
  -- huh? */
(
    "--
     --" integer identity, -- /*
    [*/] varchar(20) /* -- */
         default '*/ /* -- */' /* /* /* */ */ */
); 
go

It’s not surprising that my blog’s syntax colorer has trouble with this statement. But SQL Server will run this statement without complaining. Management Studio doesn’t even show any red squiggly lines anywhere. The same statement without comments looks like this:

create table [/*] 
(
    "--
     --" integer identity, 
    [*/] varchar(20) 
         default '*/ /* -- */' 
); 
go

I want a program to remove comments from any valid SQL and I want it to handle even this crazy example. I describe a handy method that lets me do that.

Using C#

• In your C# project, find and add a reference to Microsoft.SqlServer.TransactSql.ScriptDom. It’s available with SQL Server 2012’s Feature Pack (search for “ScriptDom” and download).
• Add using Microsoft.SqlServer.Management.TransactSql.ScriptDom; to your “usings”.
• Then add this method to your class:

  public string StripCommentsFromSQL( string SQL ) {   TSql110Parser parser = new TSql110Parser( true ); IList<ParseError> errors; var fragments = parser.Parse( new System.IO.StringReader( SQL ), out errors );   // clear comments string result = string.Join ( string.Empty, fragments.ScriptTokenStream .Where( x => x.TokenType != TSqlTokenType.MultilineComment ) .Where( x => x.TokenType != TSqlTokenType.SingleLineComment ) .Select( x => x.Text ) );   return result;   }

  public string StripCommentsFromSQL( string SQL ) { TSql110Parser parser = new TSql110Parser( true ); IList<ParseError> errors; var fragments = parser.Parse( new System.IO.StringReader( SQL ), out errors ); // clear comments string result = string.Join ( string.Empty, fragments.ScriptTokenStream .Where( x => x.TokenType != TSqlTokenType.MultilineComment ) .Where( x => x.TokenType != TSqlTokenType.SingleLineComment ) .Select( x => x.Text ) ); return result; }

… and profit! This method works as well as I hoped, even on the given SQL example.

Why I Prefer This Method

A number of reasons. By using Microsoft’s own parser, I don’t have to worry about comments in strings, or strings in comments which are problems with most T-SQL-only solutions. I also don’t have to worry about nested multiline comments which can be a problem with regex solutions.

Did you know that there’s another sql parsing library by Microsoft? It’s found at Microsoft.SqlServer.Management.SqlParser.Parser. This was the old way of doing things and it’s not supported very well. I believe this library is mostly intended for use by features like Management Studio’s Intellisense. The ScriptDom library is better supported and it’s easier to code with.

Let Me Know If You Found This Useful

Add comments below. Be warned though, if you’re a spammer, I will quickly remove your comments. I’ve had practice.