How do you parse large SQL scripts into batches?

Question

I have a very large sql file I want to break up into batches for execution. I want to make sure I'm parsing it the same way that SSMS and SQLCMD do.

Microsoft has a great mixed mode assembly named Microsoft.SqlServer.BatchParser with a class named Parser that seams like it would do the trick.

It wants an implementation of IBatchSource as an argument to SetBatchSource before calling Parse().

Where can I find an implementation of IBatchSource, and more information on how to make use of this functionality?

Answer 1

I found the assembly Microsoft.SqlServer.BatchParser in the GAC along with it's friend Microsoft.SqlServer.BatchParserClient that contains implementations the interface IBatchSource.

namespace Microsoft.SqlServer.Management.Common
{
  internal class BatchSourceFile : IBatchSource
  internal class BatchSourceString : IBatchSource
}

The following conversation then occurred.

Assembly: Hello! My name is Microsoft.SqlServer.Management.Common.ExecuteBatch. Would you like to StringCollection GetStatements(string sqlCommand)?

Me: Yes, I would, BatchParserClient assembly. Thanks for asking!

Repeatable Instructions (Do try this at home!)

• Install Microsoft SQL Server 2008 R2 Shared Management Objects
• Copy Microsoft.SqlServer.BatchParser.dll and Microsoft.SqlServer.BatchParserClient.dll from the GAC to a folder in your solution.
• Reference Microsoft.SqlServer.BatchParser & Microsoft.SqlServer.BatchParserClient

Program.cs

using System;
using System.Collections.Specialized;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;

namespace ScriptParser
{
   class Program
   {
      static void Main(string[] args)
      {
         ExecuteBatch batcher = new ExecuteBatch();
         string text = File.ReadAllText(@"Path_To_My_Long_Sql_File.sql");
         StringCollection statements = batcher.GetStatements(text);
         foreach (string statement in statements)
         {
            Console.WriteLine(statement);
         }
      }
   }
}

App.Config

<?xml version="1.0"?>
<configuration>
   <startup useLegacyV2RuntimeActivationPolicy="true">
      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5"/>
   </startup>
</configuration>

Another option is to use the ScriptDom as described in this answer: https://stackoverflow.com/a/32529415/26877.

using System;
using System.Collections.Generic;
using System.IO;
using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace ScriptDomDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            TSql120Parser parser = new TSql120Parser(false);
            IList<ParseError> errors;
            using (StringReader sr = new StringReader(@"create table t1 (c1 int primary key)
GO
create table t2 (c1 int primary key)"))
            {
                TSqlFragment fragment = parser.Parse(sr, out errors);
                IEnumerable<string> batches = GetBatches(fragment);
                foreach (var batch in batches)
                {
                    Console.WriteLine(batch);
                }
            }
        }

        private static IEnumerable<string> GetBatches(TSqlFragment fragment)
        {
            Sql120ScriptGenerator sg = new Sql120ScriptGenerator();
            TSqlScript script = fragment as TSqlScript;
            if (script != null)
            {
                foreach (var batch in script.Batches)
                {
                    yield return ScriptFragment(sg, batch);
                }
            }
            else
            {
                // TSqlFragment is a TSqlBatch or a TSqlStatement
                yield return ScriptFragment(sg, fragment);
            }
        }

        private static string ScriptFragment(SqlScriptGenerator sg, TSqlFragment fragment)
        {
            string resultString;
            sg.GenerateScript(fragment, out resultString);
            return resultString;
        }
    }
}