Parse a collection of SQL Scripts and look for batches, keywords, and potential issues.
Hey, do you automate your deployments of SQL Server scripts to your different environments? Do you review every script manually? I don't, and don't plan to start. Instead, let's automate! This function is designed to parse a given list of files to generate a summary of what type of commands are contained in them. The ideal use-case is to point this script to a file list of scripts you're going to run automatically, before you run them, to get an idea of what your changeset contains.
For this to work, you need a library: Microsoft.SqlServer.TransactSql.ScriptDom somewhere on your machine. It'll attempt to Add-Type the library for versions 11,12, and 13. If it can't find it, it'll tell you.
The script takes a [System.IO.FileInfo] input object, which means you can (and should?) pipe the results of Get-ChildItem to get a list of your files and then pass that in. Maybe something like this:
$results = Get-ChildItem .\TestScripts | .\Test-SQLScripts.ps1 -Verbose
This repository also includes some "unit tests" for the script to simulate some commands (more below in "Test Scenarios")
This function returns an object that sort of follows the base functionalty of the classes and libraries it uses. The high-level object is a roll-up of sorts that tells you what sort statements are in the scripts, like number of insert/updates/deletes/indexes, etc. If any of the statements in the file don't parse, the Error property will include the error info (which is also an object).
The object also includes a batches property, which is an array of SQL batches in the script. The batches then have a statements property, which contains the individual statements that were parsed. The individual statement objects go into more detail about what the statements are, what they do, and what they affect. To get to that info directly, you'd do this with the object you get back:
$results = Get-ChildItem .\TestScripts | .\Test-SQLScripts.ps1 -Verbose
$results.Batches.Statements | ft
ScriptName BatchNumber StatementNumber StatementType Action OnObjectSchema OnObjectName
---------- ----------- --------------- ------------- ------ -------------- ------------
AlterScript.sql 1 1 ALTER Person PErson
DeleteScript.sql 1 1 DELETE Person Person
IndexScript.sql 1 1 CREATE Person Address
IndexScript.sql 1 2 DROP Person Address
This repository also includes some "unit tests" for the script to simulate some commands (more below in "Test Scenarios")
In the TestScripts sub-folder in this repository, I have some quick SQL Scripts I wrote to test different statements. Feel free to add different scripts to this folder and run things against them to see how it returns. If it doesn't parse and return info about them, you'll need to extend the parsing...
To simplify adding new statement parsing, check out the ParserKey class I added to the script. All the tests and objects it looks for are defined as a class that is checks against. This will probably be changed to JSON soon, but for now this is how I'm handling the different statement types.