SQL Code Analysis from a PowerShell Deployment Script
This article shows how to define SQL code analysis rules using SQL Prompt and them run them automatically from a PowerShell script, displaying the detected code issues in a handy HTML report.
Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in ‘temporary’ development code, by mistake, so it is always good to have a way of flagging up SQL Code issues and ‘smells’ that are agreed to be incompatible with ‘production’ standards.
Database Developers like to agree and share a common set of styles and conventions, so that they can work easily on all parts of the database code. Where the developers need to work across applications, rather than just on the database, these pre-release checks on the source become even more important, because these checks should indicate where code review might be helpful. SQL Code Guard can work with either a database, or the source code.
We all have different views of what constitutes best-practice, so SQL Code Guard allows the team full control over what gets checked, and what is ignored. The most important aspect of checking on code is to ensure consistency. The team need to be able to share settings easily and change them when necessary. Code analysis settings are best kept in source control with the version.
Automating static code analysis on a database
SQL Code Guard’s static code analysis rules are now built-in to SQL Prompt so that developers can usually avoid checking in suspect code. The command-line version of SQL Code Guard can read the code analysis settings from SQL Prompt, and can be used simply in PowerShell. This means that SQL Change Automation, and any other PowerShell-based process, can be adapted to run automated SQL code analysis. See, for example: Practical PowerShell Processes with SQL Change Automation.
Delivery teams can create and share a configuration file, defining which rules to include or exclude, for development and build tests, so that the report that is generated reflects, consistently, the current development standards.
The current command-line version of SQL Code Guard can be downloaded from the Redgate site as a ZIP file, and should be installed in a directory, with all the files together. As long as the command-line version is fully-referenced by its path, it should work fine.
Listing 1 shows a simple way of using SQL Code Guard in PowerShell to check a live database. You will, of course, need to fill in the correct values of the parameters. Be careful, also, to ensure that your Windows user has rights to create a file at the outfile destination.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Set-Alias CodeGuard 'PathToCodeguard\SqlCodeGuard30.Cmd.exe' $params = @{ server='MyServerName' #The server name to connect Database='MyDatabase' #The database name to analyze user='MyUserName' # If no user specified then Windows authentication will be used so delete if you use Windows Athentication password='MyPassword' #user password. Delete if you use Windows Athentication outfile = 'MyPathAndFile.xml' #The file name in which to store the analysis xml report exclude='BP007;DEP004;ST001' #A semicolon separated list of rule codes to exclude include='all' #A semicolon separated list of rule codes to include } $result=codeguard @params if ($result -imatch 'error') {write-warning $result} |
Listing 1
The output of the program has interesting information such as the number of code-smells being searched, but the only word we are looking for in this example is ”Error”. There is also a log file that can be stored with the documentation of the build.
I’ll show in a moment how to view the resulting XML file, containing the code analysis findings.
Automating static code analysis on source code
You can check the code in any SQL file in any database build script (.sql file), or directory with .sql files in it. Here is a typical format of source control directory, but any directory structure is OK, as it will be searched recursively for any .sql file.
The PowerShell code is even simpler, as there are fewer parameters.
|
1 2 3 4 5 6 7 8 9 10 |
Set-Alias CodeGuard 'PathToCodeguard\SqlCodeGuard30.Cmd.exe' $params = @{ source='PathToDirectory' #The path to file or folder with /scripts (*.sql) to analyze outfile = 'MyPathAndFile.xml' #The file name in which to store the analysis xml report exclude='BP007;DEP004;ST001'#A semicolon separated list of rule codes to exclude include='all'#A semicolon separated list of rule codes to include } $result=codeguard @params if ($result -imatch 'error') {write-warning $result} |
Listing 2
Using a code analysis configuration file from SQL Prompt
If you are using the code analysis feature of SQL Prompt, you can use it to save the code analysis rules settings into a configuration file.
Within SSMS, select Manage Code Analysis rules from the SQL Prompt menu, configure which rules you want enabled or disabled, and then save your settings by clicking Save as…, changing the file location to a shared folder and clicking Save. You can also save the configuration file from the Code Guard add-in for Visual Studio. The UI saves it in %APPDATA%\SqlCodeGuard.Addin\settingsv3.xml.
Listing 3 shows the PowerShell to view the configuration file to get a list of what is being checked and what isn’t.
|
1 2 |
[xml]$XmlSettings = Get-Content -Path 'PathToSettingsFile/.casettings' $XmlSettings.SqlCodeGuardSettings.IssueSettings |
Listing 3
You use your XML configuration file with either a database or source code.
|
1 2 3 4 5 6 7 8 |
$params = @{ source='PathToDirectory' #The path to file or folder with /scripts (*.sql) to analyze config='PathToSettingsFile/.casettings' #The rules settings file to use outfile = 'MyPathAndFile.xml' #The file name in which to store the analysis xml report } $result=codeguard @params if ($result -imatch 'error') {write-warning $result} |
Listing 4
Viewing the XML file
So far so good, but the result is an XML file. There are plenty of ways of viewing it once you can get it into a table. Listing 5 shows one way to do that.
|
1 2 3 4 5 6 |
[xml]$XmlDocument = Get-Content -Path 'MyPathAndFile.xml' $XmlDocument.root.GetEnumerator() |foreach{ $name=$_.name.ToString(); $_.issue} | select-object @{Name="Object"; Expression = {$name}}, code,line,column,text|format-table |
Listing 5
Which will give something like this:
As this is scripted, there are plenty of ways of making the reporting side as elaborate as you wish, even sending email reports, the destination of which is based on the name of the object. You can even use PowerShell to attach the relevant report to the SQL script file as a block comment if you have a consistent file-naming convention that is based on the name of the object.
The simplest possibility is to convert the XML file to HTML for a website or email-based report. This can be done relatively simply in PowerShell by using the .NET XSL Translator. The XSLT file is created on the fly, so it can be parameterized to give additional information such as the server being tested or the date.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 |
$CodeGuardReport = 'MyPathAndFile.xml' $XSLTFile = "$($env:Temp)\DeleteMe.xslt" $HTMLReportFile = 'PathToWebsite\index.html' $PathToSourceCodeDirectory = 'PathToSourceCodeDirectory' $params = @{ source = $PathToSourceCodeDirectory #The path to file or folder with /scripts (*.sql) to analyze outfile = $CodeGuardReport #The file name in which to store the analysis xml report } codeguard @params <# the contents of the XSLT file #> @" <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:template match="/"> <html> <head> <title>Code Guard Results</title> <style type="text/css"> h1{ font-size:18pt; } h2{ font-size:14pt; } table{ font-size:12px; color:#333333; width:100%; border-width:1px; border-color:#a9a9a9; border-collapse:collapse; } td{ font-size:12px; border-width:1px; padding:5px; border-style:solid; border-color:#a9a9a9; } td:first-child{ font-weight:bold; } th{ font-size:12px; background-color:#b8b8b8; border-width:1px; padding:8px; border-style:solid; border-color:#a9a9a9; text-align:left; } tr{ background-color:#ffffff; } </style> </head> <body style="font-family:Verdana, Geneva, Arial, Helvetica, sans-serif" bgcolor="#ffffff"> <h1>Code Guard Results</h1> <xsl:apply-templates/> </body> </html> </xsl:template> <xsl:template match="file"> <h2><xsl:value-of select="@name"/></h2> <table> <tr><th>issue</th> <th>line</th> <th>column</th> <th>description</th> <th>severity</th> </tr> <xsl:for-each select="issue"> <tr> <td><xsl:value-of select="@code"/></td> <td><xsl:value-of select="@line"/></td> <td><xsl:value-of select="@column"/></td> <td><xsl:value-of select="@text"/></td> <td><xsl:value-of select="@severity"/></td> </tr> </xsl:for-each> </table> </xsl:template> </xsl:stylesheet> "@ > $XSLTFile $xslt_settings = New-Object System.Xml.Xsl.XsltSettings; $XmlUrlResolver = New-Object System.Xml.XmlUrlResolver; $xslt_settings.EnableScript = 1; $xslt = New-Object System.Xml.Xsl.XslCompiledTransform; $xslt.Load($XSLTFile, $xslt_settings, $XmlUrlResolver); $xslt.Transform($CodeGuardReport, $HTMLReportFile); # get-content -Path $HTMLReportFile |
Listing 6
This will give an HTML-based report like this:
Conclusions
Currently, the command-line version of SQL Code Guard is free to use, subject to the license conditions. It provides a good way of checking the code of any SQL Server database development as part of a continuous delivery. It integrates with other Redgate products. It can, for example, use the code analysis checks, as determined by the development team using SQL Prompt. It is easy to use as part of a DevOps toolchain. More than anything, though, I like it because it is a great way of saving me from embarrassment!
Tools in this post
You may also like
-
Article
SQL Server deployments: Which Redgate tools should you use?
Since 1999, we’ve been developing tools at Redgate to support database deployments for SQL Server. In the past couple of years, we’ve increased our effort in this space by further developing our proprietary technologies based on SQL Compare, and acquiring others like Flyway, the most popular database migration engine. As a result, we now offer
-
Article
Database Development in Visual Studio using SQL Change Automation: Getting Started
Steve Jones shows how to set up a SQL Change Automation (SCA) project in Visual Studio, and import an existing database. As the team make database changes, either in SSMS or VS, they import them into the SCA project, which saves each change as a migration script that is then committed to source control.
-
Article
Source controlling database users
Where developers have their own databases, you need to ensure the user accounts are configured identically across all development and test server, or you'll inevitably hear the phrase “…but it works on my machine”. Alex Yates describes, strategically, how this might work.
-
Article
Monitoring SQL Server Security: What's Required?
If we know how a database is likely to be attacked, we can arrange our lines of defense, and install the monitors required to detect any attempts. However, some types of attack are difficult to imagine, so we also need our monitoring tool to be adaptable, so that it can collect a more diffuse collection of metrics, and then help us determine the reason for any sudden change in the patterns of access .
-
Forums
SQL Change Automation Forum
Continuous integration and automated deployments for your SQL Server database
