Now I am nothing if not late to the party and so here I am rocking up to the ScriptDOM party trying to figure out where the food is and what just 5? 10 years too late? that’s good going.
DISCLAIMER: the examples below will lean heavily on some of the awesome work done here https://www.dbdelta.com/microsoft-sql-server-script-dom and here
https://www.mssqltips.com/sqlservertip/6936/find-problematic-sql-nolock-patterns-update-delete/ and without these 2 sources I don’t think I’d have got anywhere because the documentation is sparse and for someone who doesn’t have c#/.NET expertise it can be a bit daunting.
So, what is it?
scriptDOM is a code library that allows you to do analysis of T-SQL scripts to look for specific elements or just general parsing / formatting errors.
what sort of elements?
almost anything you might do in a TSQL script has a method for it https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.transactsql.scriptdom?view=sql-dacfx-150
right and how does it work?
the scripts passed in to it are broken down in to your all their individual components and then traversed to pull out the required elements
ok but why would I want to do that?
we’re only scratching the surface here but some of the key points are
and more.
couldn’t I just buy a 3rd party tool to do that?
well, yeah probably I think a lot of the tools which offer code analysis / formatting are built using the scriptDOM functionality underneath but maybe you just don’t have the budget for that or it’s not quite flexible enough for what you need.
well I’m a little intrigued is it just a button I click somewhere then?
not exactly, you’ll need to build up some scripts in these examples using powershell and .net
I’m out.
no, wait , come back it’s not that bad, stick with me we’ll get it sorted
…..
good, so the fist thing you’ll need to do if you don’t already have it is download and install the latest sqlpackage version https://docs.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-download?view=sql-server-ver15 this will allow you to run the scripts in isolation without the need for a SQL Server connection at all
that’s pretty neat.
yeah and now you’ve installed it you’ll need to load the type into your powershell session, make sure you do this first or you’ll not get very far
Add-Type -Path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
then as shown on https://www.dbdelta.com/microsoft-sql-server-script-dom/ you can use this to verify the script syntax by passing it into a parser object
$script = @"
SELECT TOP 1 column1 FROM table1
"@
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$stringReader = New-Object System.IO.StringReader($script)
$parser.Parse($stringReader, [ref]$parseErrors) |out-null
if($parseErrors.Count -gt 0) {
foreach ($parseErr in $parseErrors)
{
write-host "PARSE ERROR: Line: $($parseErr.line) offset: $($parseErr.offset) message: $($parseErr.message) " -ForegroundColor yellow
}
}
ELSE{
write-host "PARSE SUCCESS: Successfully parsed script" -ForegroundColor green
}
PARSE SUCCESS: Successfully parsed script
hooray!
now one really neat thing to note in there is this part “Microsoft.SqlServer.TransactSql.ScriptDom.TSql150Parser” in this instance we’ve targeted it against 150 = SQL2019
well that’s no good, I’m not on 2019
not surprising, luckily you can change that to target the version you need so if you’re still on SQL2012 you can just change that part to Microsoft.SqlServer.TransactSql.ScriptDom.TSql110Parser and if you then run that with a different script
$script = @"
CREATE OR ALTER PROC newproc
AS
BEGIN
SELECT 1
END
"@
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql110Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$stringReader = New-Object System.IO.StringReader($script)
$parser.Parse($stringReader, [ref]$parseErrors)|out-null
if($parseErrors.Count -gt 0) {
foreach ($parseErr in $parseErrors)
{
write-host "PARSE ERROR: Line: $($parseErr.line) offset: $($parseErr.offset) message: $($parseErr.message) " -ForegroundColor yellow
}
}
ELSE{
write-host "PARSE SUCCESS: Successfully parsed script" -ForegroundColor green
}
you’ll see this as the CREATE OR ALTER statement isn’t valid
PARSE ERROR: Line: 1 offset: 0 message: Incorrect syntax near CREATE.
PARSE ERROR: Line: 1 offset: 7 message: Incorrect syntax near OR.
what you can do then is you can parametrize the script so you can declare the version you should run as
$targetversion = 150
#$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql110Parser($true)
$str = -join("New-Object ","Microsoft.SqlServer.TransactSql.ScriptDom.TSql$($targetversion)Parser(`$true)")
$parser = invoke-expression $str
once you know that the script is valid you can start to dig in to it for the code you are looking for. As pointed out here https://www.dbdelta.com/microsoft-sql-server-script-dom/ trying to do analysis of complex statements with multiple code blocks / paths in it can be a problem and if you follow their example you’ll see that the nested select statements get missed but thankfully they have provided an example of a class using Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor which you can declare in your scripts to make this much more usable so we’ll use that framework to help us start to dig in to the scripts. In fact if you didn’t at the start of this read the links, go do that now it’s going to help you for the next section. I’ll wait.
OK, BRB
now we’re going to carry on with some more examples and for this we’re going to use a longer script and look for the following
- cursor
- transaction without a commit
- select from a VERY large table
- select *
OMG, WHO WROTE THIS? IS THIS IN PRODUCTION??
no, it’s just an example
phew, ok
let’s crack on, to start with we’re going to look for the cursor in there, we’ll declare the class, assign the parse results to a variable and then let the two collide
Add-Type -Path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$script = @"
SELECT * FROM dbo.tableo with (nolock);
IF 1 = 1
BEGIN
SELECT * FROM dbo.superbigtable;
END;
DECLARE @name VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
BEGIN TRANSACTION
select 1
IF get-date <getdate
BEGIN
--COMMIT
SELECT 'no commit'
END
ELSE
BEGIN
ROLLBACK
END
"@
$targetversion = 150
class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.CursorDefinition] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
}
$str = -join("New-Object ","Microsoft.SqlServer.TransactSql.ScriptDom.TSql$($targetversion)Parser(`$true)")
$parser = invoke-expression $str
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$stringReader = New-Object System.IO.StringReader($script)
$fragment = $parser.Parse($stringReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0) {
foreach ($parseErr in $parseErrors)
{
write-host "PARSE ERROR: Line: $($parseErr.line) offset: $($parseErr.offset) message: $($parseErr.message) " -ForegroundColor yellow
}
}
ELSE{
write-host "PARSE SUCCESS: Successfully parsed script" -ForegroundColor green
}
IF($parseErrors.Count -ieq 0)
{
$visitor = [MyVisitor]::new()
$fragment.Accept($visitor)
}
PARSE SUCCESS: Successfully parsed script
WARNING: CursorDefinition found at line 10, column 1, length 42
You can see in this example it’s flagged up the cursor because we used the Microsoft.SqlServer.TransactSql.ScriptDom.CursorDefinition to identify it. Let’s add the Select * example from https://www.dbdelta.com/microsoft-sql-server-script-dom/ just to show you can use the two together
class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.SelectStarExpression] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.CursorDefinition] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
}
PARSE SUCCESS: Successfully parsed script
WARNING: SelectStarExpression found at line 1, column 8, length 1
WARNING: SelectStarExpression found at line 4, column 12, length 1
WARNING: CursorDefinition found at line 10, column 1, length 42
that has confirmed the parsing but warned on both select * statements and the cursor, so you can see how you can quickly build up a list of your troubles.
I guess so yeah
let’s take a look at identifying the transactions next as this caused me some headaches. I had hoped that using some combination of
Microsoft.SqlServer.TransactSql.ScriptDom.BeginTransactionStatement
Microsoft.SqlServer.TransactSql.ScriptDom.CommitTransactionStatement
Microsoft.SqlServer.TransactSql.ScriptDom.RollbackTransactionStatement
to identify the script elements would do it and whilst in isolation they all work
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.BeginTransactionStatement] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.CommitTransactionStatement] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.RollbackTransactionStatement] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
WARNING: BeginTransactionStatement found at line 27, column 1, length 17
WARNING: RollbackTransactionStatement found at line 36, column 1, length 8
note that there is no commit in the script we used so it doesn’t find it which is what I’d expect but I haven’t found a way to nest the checks successfully to check that ‘yes I found a begin transaction’ but ‘no I didn’t find a commit’, if you’ve managed this one then I’d be really interested to see how that’s working.
BOOOOO!!HISSSSS!
You can’t do that you said you’d tell me how to find them?!
WHY HAVE I EVEN READ THIS FAR?!
I do have a workaround though.
oh, sorry,
if you did your homework you’ll have seen here https://www.mssqltips.com/sqlservertip/6936/find-problematic-sql-nolock-patterns-update-delete/ how you can interrogate the types and statements within a fragment and use these to find things such as NOLOCKs well if we add in this section to look at the fragments.
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.BeginTransactionStatement] $fragment) {
write-host ($fragment.ScriptTokenStream|select-object tokentype)
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
you’ll get someting like this , not very useful right?
{TokenType=Select} @{TokenType=WhiteSpace} @{TokenType=Star} @{TokenType=WhiteSpace} @{TokenType=From} @{TokenType=WhiteSpace} @{TokenType=Identifier} @{TokenType=Dot} @{TokenType=Identifier} @{TokenType=WhiteSpace} @{TokenType=With} @{TokenType=WhiteSpace} @{TokenType=LeftParenthesis} @{TokenType=Identifier} @{TokenType=RightParenthesis} @{TokenType=Semicolon} @{TokenType=WhiteSpace} @{TokenType=If} @{TokenType=WhiteSpace} @{TokenType=Integer} @{TokenType=WhiteSpace}……
too much token is a bad thing
yeah not ideal but buried in there you’ll find these ones
@{TokenType=Transaction}
@{TokenType=Rollback}
and when there is a commit in the script you’ll also get
@{TokenType=Commit}
keep going….
so now we know these are there we have something to work with and we can check that a COMMIT comes after a BEGIN TRANSACTION when a transaction is found
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.BeginTransactionStatement] $fragment) {
[int]$start = ($fragment.ScriptTokenStream|WHERE-object{$_.TokenType -in ("Transaction")}).offset
[int]$end =($fragment.ScriptTokenStream|WHERE-object{$_.TokenType -in ("COMMIT")}).offset
IF($null -eq $end){$end = 0}
#$start
IF($end -lt $start){
Write-Host "WARNING: $($Fragment.GetType().Name) BEGIN TRANSACTION without a COMMIT" -ForegroundColor Yellow}
}
PARSE SUCCESS: Successfully parsed script
WARNING: SelectStarExpression found at line 1, column 8, length 1
WARNING: SelectStarExpression found at line 4, column 12, length 1
WARNING: CursorDefinition found at line 10, column 1, length 42
WARNING: BeginTransactionStatement BEGIN TRANSACTION without a COMMIT
Ta-da, now if you remove the comment from the commit in the script
–COMMIT
and run it again you’ll see that we no longer get a warning about the transaction
PARSE SUCCESS: Successfully parsed script
WARNING: SelectStarExpression found at line 1, column 8, length 1
WARNING: SelectStarExpression found at line 4, column 12, length 1
WARNING: CursorDefinition found at line 10, column 1, length 42
that’s more like it
the last section we’re going to look at is how to pick out those scripts that come in that will touch that VERY large table you have, the one you kept saying you really needed to put partitioning on but now is so big it has it’s own orbit. You need to keep an eye on the Selects from that and definitely the Updates.
For that we’ll use the Microsoft.SqlServer.TransactSql.ScriptDom.NamedTableReference which will allow us to see the tables referenced in the script and look for those known problems
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.NamedTableReference] $fragment) {
IF($fragment.SchemaObject.BaseIdentifier.Value -ieq "superbigtable")
{
Write-Host "WARNING: Large table. Previously identified troublesome table identified $($fragment.SchemaObject.BaseIdentifier.Value)" -ForegroundColor Yellow
}
}
WARNING: Large table. Previously identified troublesome table identified superbigtable
that’s handy
as you can see there’s a lot to it and and we haven’t even scratched the surface here, it’s really good for opt in analysis where you are looking for specific things.
I’ll add the full script of these examples below for reference and as a CHALLENGE I haven’t added in the check to find the NOLOCK, see if you can update the script to alert for that too.
more homework?
yeah sorry but I’d be really keen to know where you’ve found a use for scriptDOM and how you are using it
Add-Type -Path "C:\Program Files\Microsoft SQL Server\150\DAC\bin\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$script = @"
SELECT * FROM dbo.tableo with (nolock);
IF 1 = 1
BEGIN
SELECT * FROM dbo.superbigtable;
END;
DECLARE @name VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
BEGIN TRANSACTION
select 1
IF get-date <getdate
BEGIN
--COMMIT
SELECT 'no commit'
END
ELSE
BEGIN
ROLLBACK
END
"@
$targetversion = 150
class MyVisitor: Microsoft.SqlServer.TransactSql.ScriptDom.TSqlConcreteFragmentVisitor {
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.SelectStarExpression] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.CursorDefinition] $fragment) {
Write-Host "WARNING: $($Fragment.GetType().Name) found at line $($fragment.StartLine), column $($fragment.StartColumn), length $($fragment.FragmentLength)" -ForegroundColor Yellow
}
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.BeginTransactionStatement] $fragment) {
[int]$start = ($fragment.ScriptTokenStream|WHERE-object{$_.TokenType -in ("Transaction")}).offset
[int]$end =($fragment.ScriptTokenStream|WHERE-object{$_.TokenType -in ("COMMIT")}).offset
IF($null -eq $end){$end = 0}
#$start
IF($end -lt $start){
Write-Host "WARNING: $($Fragment.GetType().Name) BEGIN TRANSACTION without a COMMIT" -ForegroundColor Yellow
}
}
#check for big tables
[void]Visit ([Microsoft.SqlServer.TransactSql.ScriptDom.NamedTableReference] $fragment) {
#Write-Host "Found $($fragment.GetType().Name) at line $($fragment.StartLine), column $($fragment.StartColumn). Table name: $($fragment.SchemaObject.BaseIdentifier.Value)"
IF($fragment.SchemaObject.BaseIdentifier.Value -ieq "superbigtable")
{
Write-Host "WARNING: Large table. Previously identified troublesome table identified $($fragment.SchemaObject.BaseIdentifier.Value)" -ForegroundColor Yellow
}
}
}
$str = -join("New-Object ","Microsoft.SqlServer.TransactSql.ScriptDom.TSql$($targetversion)Parser(`$true)")
$parser = invoke-expression $str
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]
$stringReader = New-Object System.IO.StringReader($script)
$fragment = $parser.Parse($stringReader, [ref]$parseErrors)
if($parseErrors.Count -gt 0) {
foreach ($parseErr in $parseErrors)
{
write-host "PARSE ERROR: Line: $($parseErr.line) offset: $($parseErr.offset) message: $($parseErr.message) " -ForegroundColor yellow
}
}
ELSE{
write-host "PARSE SUCCESS: Successfully parsed script" -ForegroundColor green
}
IF($parseErrors.Count -ieq 0)
{
$visitor = [MyVisitor]::new()
$fragment.Accept($visitor)
}
I need a lay down now
Secret SQL 