1 July 2021

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Phil Factor

1 July 2021

Managing, Editing and Comparing SQL Code Analysis Settings

If you need to manage multiple code analysis settings files, per team or database, you'll want a good way to document and manage the files, and to compare two files to see what changed.

Guest post

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!

SQL Code Analysis settings

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.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

[xml]$Casettings=@"

<?xml version="1.0"?>

<UseTrustedConnection>false</UseTrustedConnection>

<Pattern>^THROW$</Pattern>

</NC001d>

<CGUNP>Warning</CGUNP>

<!-- Best practice rules

--> <BP001>Warning</BP001>

<BP002>Warning</BP002>

<BP003>Warning</BP003>

<BP004>Warning</BP004>

<BP005>Warning</BP005>

<BP006>Warning</BP006>

<BP007>Warning</BP007>

<BP008>Warning</BP008>

<BP009>Warning</BP009>

<BP010>Warning</BP010>

<BP011>Warning</BP011>

<BP012>Warning</BP012>

<BP013>Warning</BP013>

<BP014>Warning</BP014>

<BP015>Warning</BP015>

<BP016>Warning</BP016>

<BP017>Warning</BP017>

<BP018>Warning</BP018>

<BP022>Warning</BP022>

<BP023>Warning</BP023>

<BP024>Warning</BP024>

<!-- Deprecated Syntax rules

--> <DEP001>Warning</DEP001>

<DEP002>Warning</DEP002>

<DEP003>Warning</DEP003>

<DEP006>Warning</DEP006>

<DEP007>Warning</DEP007>

<DEP009>Warning</DEP009>

<DEP011>Warning</DEP011>

<DEP012>Warning</DEP012>

<DEP013>Warning</DEP013>

<DEP014>Warning</DEP014>

<DEP015>Warning</DEP015>

<DEP016>Warning</DEP016>

<DEP018>Warning</DEP018>

<DEP019>Warning</DEP019>

<DEP020>Warning</DEP020>

<DEP021>Warning</DEP021>

<DEP022>Warning</DEP022>

<DEP025>Warning</DEP025>

<DEP026>Warning</DEP026>

<DEP027>Warning</DEP027>

<!-- Execution Rules

--> <EI003>Warning</EI003>

<EI011>Warning</EI011>

<EI012>Warning</EI012>

<EI013>Warning</EI013>

<EI015>Warning</EI015>

<EI016>Warning</EI016>

<EI021>Warning</EI021>

<EI023>Warning</EI023>

<EI024>Warning</EI024>

<EI028>Warning</EI028>

<EI029>Warning</EI029>

<EI030>Warning</EI030>

<!-- Miscellaneous rules

--> <MI001>Warning</MI001>

<MI002>Warning</MI002>

<MI003>Warning</MI003>

<MI005>Warning</MI005>

<MI006>Warning</MI006>

<MI007>Warning</MI007>

<MI008>Warning</MI008>

<!-- Performance Rules

--> <PE001>Warning</PE001>

<PE002>Warning</PE002>

<PE003>Warning</PE003>

<PE004>Warning</PE004>

<PE005>Warning</PE005>

<PE006>Warning</PE006>

<PE007>Warning</PE007>

<PE008>Warning</PE008>

<PE010>Warning</PE010>

<PE011>Warning</PE011>

<PE012>Warning</PE012>

<PE013>Warning</PE013>

<PE014>Warning</PE014>

<PE015>Warning</PE015>

<PE016>Warning</PE016>

<PE017>Warning</PE017>

<PE018>Warning</PE018>

<PE019>Warning</PE019>

<PE020>Warning</PE020>

<PE021>Warning</PE021>

<PE023>Warning</PE023>

<!-- Script Rules

--> <SC004>Warning</SC004>

<SC005>Warning</SC005>

<SC006>Warning</SC006>

<!-- Style Rules

--> <ST001>Warning</ST001>

<ST002>Warning</ST002>

<ST003>Warning</ST003>

<ST006>Warning</ST006>

<ST007>Warning</ST007>

<ST008>Warning</ST008>

<ST010>Warning</ST010>

<ST011>Warning</ST011>

<ST013>Warning</ST013>

</IssueSettings>

<UseCompression>false</UseCompression>

</SqlCodeGuardSettings>

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…

1	$Casettings.Save("$env:LOCALAPPDATA\Red Gate\SQL Prompt 10\CodeAnalysis\CodeAnalysisSettings.casettings")

…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.

documented sql code analysis settings

(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:

[xml]$SourceXML='';[xml]$TargetXML=''

# read in the source

$SourceXML.Load('C:\Users\Phil\AppData\Local\Red Gate\SQL Prompt 10\CodeAnalysis\CodeAnalysisSettings.casettings')

# read in the target

$TargetXML.Load('C:\Users\Phil\AppData\Local\Red Gate\SQL Prompt 10\CodeAnalysis\CodeAnalysisSettingsCopy.casettings')

compare-Settings $SourceXML $TargetXML |Out-GridView

comparing sql code analysis settings

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:

1 2	compare-Settings $SourceXML $TargetXML\| where {$_.Match -eq '<>'}\|Select Ref,Source, target }

listing which code analysis settings changed

Comparing code formatting styles

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

$Source=get-content 'C:\Users\Phil\AppData\Local\Red Gate\SQL Prompt 10\Styles\Phils Style.json'|

convertFrom-json

$Target =get-content 'C:\Users\Phil\AppData\Local\Red Gate\SQL Prompt 10\Styles\Active Style (old).json'|

convertFrom-json

compare-Settings $Source $Target |Out-GridView

comparing sql formatting styles

The Compare-Settings function

Here is the code for the function

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

.SYNOPSIS

Used to Compare two SQL Prompt Code analysis settings files or

style Format files

.DESCRIPTION

This compares two objects that are either XML objects or are derived from

first reading in JSON Files and converting them, using Convertfrom-JSON.

This compares two PowerShell objects but because the styles or CA Settings do not

have any value arrays, it doesn't bother to deal with that.

.PARAMETER Ref

The source object derived from ConvertFrom-JSON or the XML object

.PARAMETER diff

The target object derived from ConvertFrom-JSON or the XML object

.PARAMETER Avoid

a list of any object you wish to avoid comparing

.PARAMETER Parent

Only used for recursion

function Compare-Settings

{

param

(

[Parameter(Mandatory = $true, #The source object derived from ConvertFrom-JSON

Position = 1)]

[object]$Ref,

[Parameter(Mandatory = $true, #The target object derived from ConvertFrom-JSON

Position = 2)]

[object]$diff,

[Parameter(Mandatory = $false,

Position = 3)]

[object[]]$Avoid=@('Metadata', '#comment'),

[Parameter(Mandatory = $false,

Position = 4)]

[string]$Parent = ''

)

# first create a unique (unduplicated) list of all the key names obtained from

# either the source or target object

[string[]]($Ref | gm -MemberType Properties | foreach{ $_.Name }) +

($Diff | gm -MemberType Properties | foreach{ $_.Name }) | select -Unique |

foreach{ # see if the key is there and if so what type of value it has

$Name = $_;

$Type = $Null; #because we don't know it and it may not exist

$SourceValue = $null; #we fill this where possible

$TargetValue = $null; #we fill this where possible

$ItsAnObject=$null;

$ItsAComparableValue=$Null;

$FullName = $Parent + $Name #as a display reference

if ($Name -notin $Avoid) #if the user han't asked for it to be avoided

{

write-Verbose "Checking $Name"

$Match = $null;

if ($Diff.($Name) -eq $null) #if it isn't in the target

{

$match = '<-' #meaning only in the source

$SourceValue = $Ref.($Name) #logically the source has a value

}

elseif ($Ref.($Name) -eq $null) #if it isn't in the source

{

$match = '->' #meaning only in the target

$TargetValue = $Diff.($Name)# and logically the target has a value

}

if ($Match -eq '->') #where do we hget the type?

{

$Type = $diff.($Name).GetType().Name #we'll see if it is a simple value

$BaseType=$diff.($Name).GetType().BaseType

}

else

{

$Type = $Ref.($Name).GetType().Name #we'll see if it is a simple value

$BaseType=$Ref.($Name).GetType().BaseType

}

if ($match -eq $null) #then it is in both source and target!

{

$SourceValue = $Ref.($Name) #get the source values

$TargetValue = $Diff.($Name)

$ItsAComparableValue=($Type -in ('String', 'Int32', 'int64', 'Boolean', 'null'))

if ($ItsAComparableValue)

{

#just compare the velues

if ($Diff.($Name) -eq $ref.($Name))

{ $match = '==' } # the same

else { $match = '<>' } # different

}

$ItsAnObject = ((!($ItsAComparableValue)) -and

$BaseType -in 'System.Xml.XmlLinkedNode','System.Xml.XmlNode','System.Object')

; #is it an object?

#create a sensible display for object values

$DisplayedValue = @($SourceValue, $TargetValue) | foreach{

if (!($ItsAnObject)) { $_ }

elseif ($_ -ne $Null) { '(object)' }

else { '' }

}

if ($Match -ne $Null)

{# create the next row of our 'table' with a pscustomobject

1 | Select @{ Name = 'Ref'; Expression = { $FullName } },

@{ Name = 'Source'; Expression = { $DisplayedValue[0] } },

@{ Name = 'Target'; Expression = { $DisplayedValue[1] } },

@{ Name = 'Match'; Expression = { $Match } }

}

elseif ($ItsAnObject) #if it is an object on both sides

{ Compare-Settings $Ref.($Name) $Diff.($Name) $Avoid "$Parent$Name." }

# call the routine recursively

else { write-warning "No idea what to do with $($Name) $($Type) $($BaseType)" }

if ($Type -ne $Null)

{ write-Verbose "compared [$Type]$FullName ($Basename) with $match match" }

}

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

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product articles
SQL Prompt
SQL Code Analysis
Managing, Editing and Comparing SQL…

Guest post

Managing, Editing and Comparing SQL Code Analysis Settings

Guest post

Generating and using a code analysis settings file

Editing the settings in SQL Prompt

Roll yourself a documented settings file

Comparing code analysis settings

Comparing code formatting styles

The Compare-Settings function

Conclusions

Tools in this post

SQL Prompt

SQL Prompt

Product Articles

University

Events

Forums

Community

Simple Talk

Redgate Test Data Manager

Redgate Flyway

Redgate Monitor

Overview

Protect

Automate

Monitor

Product articles SQL Prompt SQL Code Analysis Managing, Editing and Comparing SQL…

Guest post

Guest post

Generating and using a code analysis settings file

Editing the settings in SQL Prompt

Roll yourself a documented settings file

Comparing code analysis settings

Comparing code formatting styles

The Compare-Settings function

Conclusions

Tools in this post

SQL Prompt

SQL Prompt

You may also like

Redgate Hub

Product Articles

University

Events

Forums

Community

Simple Talk

Product articles
SQL Prompt
SQL Code Analysis
Managing, Editing and Comparing SQL…