SQL Server ScriptDom Parsing

Question

The team of developers I work with are using SQL Data Projects for a large piece of work we have to do against an existing database. We are a few weeks in and there have been a few gotchas, but the experience has been generally good.

However, when we get to deploy to production, the dba team have refused to accept DACPACs as a deployment method. Instead, they are want to see a traditional script per DML or DDL statement.

The current thinking is to create a difference script between the finished SQL project and the production environment, and then parse that into individual scripts. Not nice I know.

To parse the difference script there seems to be two options:

1. Parse the script based on the batch separator command, GO. A rather basic solutions but shows promise.
2. Or, use the Microsoft.SqlServer.TransactSql.ScriptDom. This looks more future proof but seems far more complex.

I'm trialling the ScriptDom at the moment but am having trouble understanding it. My current, but not only issues, is as follows.

I'm trying to parse the following SQL using the ScriptDOM in C#:

CREATE TABLE dbo.MyTable
(
    MyColumn VARCHAR(255)
)

But cannot see how to access the VARCHAR size, in this case, 255.

The code I'm using is as follows:

TSqlFragment sqlFragment = parser.Parse(textReader, out errors);

SQLVisitor myVisitor = new SQLVisitor();
sqlFragment.Accept(myVisitor);

public override void ExplicitVisit(CreateTableStatement node)
{
    // node.SchemaObjectName.Identifiers to access the table name
    // node.Definition.ColumnDefinitions to access the column attributes
}

From each column definition I expected to find a length property or similar. However, I also have a sneaking suspicion that you can use the Visitor Pattern, which I struggle with, to reparse each column definition. Any ideas?

Answer 1

Great that you are using ssdt!

The easiest way to handle this when you have DBA's who don't want to work with dacpacs is to pre-generate the deloyment script using sqlpackage.exe.

The way I do it is...

• Check t-sql code into project
• Build server builds ssdt project
• Deploy and run tests on ci server
• use sqlpackage.exe /action:script to compare the dacpac to QA, PROD etc and generate a deployment script.

The DBA's then take that script (or when we are ready we tell them the build number to grab) - they can the peruse and deploy that script.

Things to note:

• You will need access to the prod db or a mirror copy you can use, you do not need dbo or anything just permissions in (https://the.agilesql.club/Blogs/Ed-Elliott/What-Permissions-Do-I-Need-To-Generate-A-Deploy-Script-With-SSDT)
• The scripts are only valid until the schema in the prod db changes - so if you generate 4 scripts the and run script 1, the other three are invalid and you will need to re-run a build to generate the script.

If you don't have CI setup you can just use sqlpackage.exe to generate the script without the automatic bits :)

Hope it helps!

ed

Answer 2

I don't think you need a visitor here at all. If I understand your goal correctly, you'd like to take the TSQL generated by SSDT, parse it using SQLDOM and then print the batches individually. The code to do that would look something like this:

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;
        }
    }
}

As for how to work with these ASTs, I find it easiest to use Visual Studio's debugger to visualize the tree, because you can see the actual type of each node and all of its properties. It takes just a little bit of code to parse the TSQL, as you can see.

Answer 3

#reference Microsoft.SqlServer.BatchParser
#reference Microsoft.SqlServer.BatchParserClient

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("ASqlFile.sql");
         StringCollection statements = batcher.GetStatements(text);
         foreach (string statement in statements)
         {
            Console.WriteLine(statement);
         }
      }
   }
}