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