• SQL Prompt
• SQL Code Analysis

Managing, Editing and Comparing SQL Code Analysis Settings

SQL code analysis is the process of scanning code to flag up deprecated features, ambiguities or other forms of technical debt. It can uncover a comprehensive range of all the quirks, code smells and performance gotchas that have been discussed within the SQL Server community of developers over the years.

Having written many articles on code analysis, and even collated SQL code smells in a book, I take a rather liberal stance. A smell doesn’t necessarily indicate rot: it may just be an unusual fragrance. I therefore like to see everything that needs to be double-checked, but I’m relaxed about allowing some smells. However, my experience of code reviews tells lurking behind an apparently trivial rule breakage in the code is often a more significant problem.

That said, not every code analysis rule is appropriate in every circumstance; you select which rules you want to follow and which you don’t, and all the settings are saved in an XML file called, rather quaintly, CAsettings. Within Redgate tools, there are a few ways that you’d come across code analysis settings. SQL Prompt runs code analysis as you type in or review code in SSMS. SQL Change Automation runs a set of analysis rules during builds. You can also still use the original “Code Guard” command-line SQL Code Analysis.

In each case, you’ll want these settings to be to your liking, or rather to conform with your organization’s policy, and you’d want them to be consistent. Also, if you use different rules for the various developments, teams, databases or developers, it becomes useful to be able to view these files, manage them, or to compare them.

I’ll demonstrate how to create and use more than one settings file, with SQL Prompt, and then how to create your own settings files from scratch, using PowerShell or a text editor. I’ll show how editing an XML settings file in a text editor can be made less painful or erratic. Finally, I provide a PowerShell function that will compare two settings files and give you a side-by-side comparison of the values for every setting, or just a quick list of the changed settings.

Generating and using a code analysis settings file

SQL Prompt gives you a Code Analysis Settings (CASettings) file with all the rules enabled, and you can alter the rules to taste, in the ‘Code Analysis Rules’ dialog box. You can make new files with the ‘Save As’ button, to create as many different settings files as you need. You may, for example, wish to have both strict and lax files depending on your team, the nature of your work or your mood. You can select the file that you wish to be the current file or specify a different folder. It is also possible to share a team CAsettings file.

Once you’ve saved a CAsettings file, you can use it in any Redgate tool that does code analysis. For example, in SCA, you can apply one to the New-DatabaseReleaseArtifact cmdlet through the '-CodeAnalysisSettingsPath' argument. You just provide a valid path to the settings file. With the command line tool, there is a /config argument (/c for short) that similarly requires a valid path to a config file

If you need the rules to be applied differently in different databases, then with the command line tool, or SQL Change Automation, you can, for example, specify the tables for which certain rules apply via ‘skip procedures’.

Editing the settings in SQL Prompt

The CAsettings file is in XML. The actual settings that you change are so simple that it isn’t a great deal of hassle to inspect them. When using these settings for SQL Code Analysis, you can disable a code analysis rule by setting it to ‘Ignore’, or else configure each setting to either ‘Warning’ or ‘Error’. The ‘Error’ will be communicated to the calling script or application via a return code of 1. As shipped, all rules are set to ‘Warning’.

I created the following CAsettings file by randomly disabling tests in SQL Prompt. This is for illustration only, not as a recommendation!

You’ll immediately see the problem with this. The names of the various rules aren’t intuitive. You’d be continually looking them up to work out the rule to which “BP015” refers…

Roll yourself a documented settings file

…What I do instead is to generate a CAsettings file, either in a text editor or a PowerShell script and save it as XML source code. I add comments to my template to explain each setting. These commented files can be used happily by any tool that does code analysis, but they aren’t preserved if you edit an XML file in SQL Prompt.

To save you some time here is a SQL Prompt CAsettings file with the default settings, with every setting commented, done as a PowerShell script so I can provide a GUID.

In the above code, I’ve assigned the document to an XML variable which validates it and turns it into an XML object. When this is saved in PowerShell from the object…

…it adds the comments to the document. This means that from now on, you can edit the document in any text editor that comes to hand, with enough information to guide you successfully, but it is read into Prompt or SQL Code Analysis correctly.

(Geeky detail: in an XML object, these comments are considered nodes of XmlComment type.)

The advantage of using PowerShell over a text editor is that it validates your text when it converts it into an XML object, with the GUID, and saves it nicely formatted. If you save it directly from a text editor, you must do your own validation on it, and provide the GUID beforehand.

Comparing code analysis settings

Once you have several settings files, you’ll be struck by the difficulty of working out the differences between them. You might also be using a shared settings over a local network fileserver and want to see what changes are being made to it. I’ve written a PowerShell function, Compare-Settings, that allows you to compare CAsettings files directly (I show the full code for the function a bit later). Here, I’m using it to compare my current SQL Prompt code analysis settings file to an older version to see what’s changed:

The match symbols mean:

• == Both equal
• <- only in source
• -> only in target
• <> not equal

You can, of course, just get a list of changed rules:

Comparing code formatting styles

This same routine will also compare Prompt formatting styles (see How to Compare Two SQL Prompt Styles):

The Compare-Settings function

Here is the code for the function

Conclusions

Code Analysis is increasingly important because it helps flag possible issues before they trip up the smooth deployment process. Even if you know that your SQL Code is pure and beyond reproach, it could still fail a test or get objected-to by someone in the sign-off process. Because the preferences for this sort of analysis can be fine-tuned, it is possible to end up having several of them for different parts of the development process, for individual teams or databases, it would make sense to have the means to administer, inspect or edit them.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more