Parsing scripts with ScriptDOM

In the last post I wrote about what ScriptDOM is and why it is useful. From this post, I will explain how it can be put to use. What it does when you pass a script to it is to parse it, check if it is free of syntax errors, and build what is called an ‘Abstract Syntax Tree’, which is a programmatic representation of the script, with nodes and branches for each code element. The rest of the usage/functionality is built around the Abstract Syntax Tree. So in this post let us look into how this is accomplished.

1 Download ScriptDOM from here. There are separate versions for .NET core and .NET framework. I will be using the latter in my blog posts. All you need is the library Microsoft.SqlServer.TransactSql.ScriptDom.dll – you can search where it is installed and copy it somewhere for your use, or leave it where it is. The default location is C:Program FilesMicrosoft SQL Server<version>DACbin

2 Add a reference to the DLL as the first line of the PowerShell script – like below.

Add-Type -Path "C:Program FilesMicrosoft SQL Server150DACbinMicrosoft.SqlServer.TransactSql.ScriptDom.dll"

3 Create the parser object with a compatibility level that is appropriate to the SQL server database you are planning to deploy the scripts on or where scripts already exist. I used 150, you go all the way back to 90.

$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)

4 Create the object that will hold syntax errors if any.

$SyntaxErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
5 Parse the file into strings using streamreader, and then call the parser object with strings to check for syntax errors.

$stringreader = New-Object -TypeName System.IO.StreamReader -ArgumentList $Script

6 Build an abstract syntax tree from string reader object.

$tSqlFragment = $parser.Parse($stringReader, [ref]$SyntaxErrors)

Then we check if the syntaxerrors object has anything in it, which means there are errors. I removed a comma after the first column and

7 Example:

I have a script as below in which I have introduced a syntax error. I removed a comma after the first column.

USE [AdventureWorks2019]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [HumanResources].[Department](
[DepartmentID] [smallint] IDENTITY(1,1) NOT NULL
[Name] [dbo].[Name] NOT NULL,
[GroupName] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
CONSTRAINT [PK_Department_DepartmentID] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I pass this script to the syntax checking function as below. (The code for the entire function is below this post).
Find-SyntaxErrors c:scriptdomcreatetable.sql

My results are as below.

I add a comma,fix the error, and re-run it – my results are as below

This is hence an easy, asynchronous way to check for syntax errors in code. To be aware though that it cannot validate objects (check for valid column names/table names etc) as it is not connected to any SQL Server.

The output of a syntactically clean script is the abstract syntax tree object – which can be used to find patterns. In the next post, we can look at how to use it to find what we need in the code. The complete code is as below. Thanks for reading!