How to Compare Two SQL Prompt Styles
A PowerShell function that will compare two SQL formatting styles, saved in JSON, and produce a report showing the differences between the options they use to lay out your SQL code.
What’s in a SQL Prompt formatting style?
One of the more interesting recent changes to SQL Prompt (since v10.5) has been, for me, the change to JSON-based definitions for formatting styles. It is not just styles that have a new JSON file but snippets too. It won’t be long, I suspect, before code analysis rules follows suit. This has the great benefit to me to be able to easily check to see what has changed in the settings, or to spot the difference between two styles. I realize that if you store them in source control then you can find out the same thing, but perhaps not so elegantly or rapidly. I like to be able to see a result like this, showing me side-by-side differences in the formatting options between my different formatting styles.
With an object like this, you can list the one that match (==), the ones that don’t (<>), the ones that only exist in the source (<-) or even the ones that only exist in the target. (->). Because the keys are mainly self-explanatory, you can tell quickly what is being specified in this style.
Where the styles are stored
The styles that you create, customize, share or import used to be stored as XML files, with the file extension sqlpromptstyle or sqlpromptstylev2. They are now stored as a json file. It is easy to convert them from the old to the new format.
Although the old format could be compared easily, they weren’t nearly as well-structured as the new JSON files, which makes it a lot easier. As well as styles, there is a ‘Metadata’ object that provides the name and id for the style. Though this metadata is potentially useful (a created/modified date would be good here too), it really shouldn’t be included in a comparison.
Unless you have changed the path of the Style folder to access a shared drive, these styles are stored here:
|
1 |
"$env:LOCALAPPDATA\Red Gate\SQL Prompt 10\Styles" |
If a team is sharing a style, it becomes even more useful because it means you can compare your copy of a style to the version you agreed with the team and check for changes!
The PowerShell function for comparing styles
All the main work is done in a function that returns an object that is easy to format.
|
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 93 94 95 96 97 98 99 100 101 102 103 |
<# .SYNOPSIS Used to Compare two SQL Prompt JSON style files .DESCRIPTION This compares two objects that are derived from first reading in JSON Files and converting them, using Convertfrom-JSON. This compares two PowerShell objects but because the styles do not have any value arrays, it doesn't bother to deal with that. .PARAMETER Ref The source object derived from ConvertFrom-JSON .PARAMETER diff The target object derived from ConvertFrom-JSON .PARAMETER Avoid a list of any object you wish to avoid comparing .PARAMETER Parent Only used for recursion #> function Compare-Styles { 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'), [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 $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 $Type = $Ref.($Name).GetType().Name #and we can get its type } elseif ($Ref.($Name) -eq $null) #if it isn't in the source { $match = '->' #meaning only in the target $Type = $Diff.($Name).GetType().Name #we can get its type $TargetValue = $Diff.($Name)# and logically the target has a value } if ($match -eq $null) #then it is in both source and target! { $Type = $Ref.($Name).GetType().Name #we'll see if it is a simple value $SourceValue = $Ref.($Name) #get the source values $TargetValue = $Diff.($Name) if ($Type -in ('String', 'Int32', 'int64', 'Boolean', 'null')) { #just compare the velues if ($Diff.($Name) -eq $ref.($Name)) { $match = '==' } # the same else { $match = '<>' } # different } } $ItsAnObject = ($Type -ieq 'pscustomobject'); #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-Styles $Ref.($Name) $Diff.($Name) $Avoid "$Parent$Name." } # call the routine recursively else { write-warning "No idea what to do with $($Name)" } if ($Type -ne $Null) { write-Verbose "compared [$Type]$FullName with $match match" } } } } |
You can use it like this
|
1 2 3 4 5 |
$Source=get-content 'PathToTheSourceFile'| convertFrom-json $Target =get-content 'PathToTheTargetFile'| convertFrom-json Target Compare-Styles $Source $Target| Out-GridView |
You can filter for just those whose values had changed
|
1 |
Compare-Styles $Source $Target | where {$_.Match -eq '<>'} |
Or those with values only in the target
|
1 |
Compare-Styles $Source $Target | where {$_.Match -eq '->'} |
…and so on.
You can specify the objects you don’t want to investigate for the comparison
|
1 |
Compare-Styles $Source $Target @('Metadata','ddl','CaseExpressions','dml') |
You can export the result as a CSV or anything else you like
|
1 |
Compare-Styles $new $old| ConvertTo-CSV |
Conclusion
At this point you might be thinking at this point that I’ve gone to a lot of trouble unnecessarily because PowerShell already has a Compare-Object cmdlet that would do the trick. I sometimes think that this cmdlet is a sort of private joke of the PowerShell development team. It works fine but trying to control it or modify the way it works is like attempting to control a cornered rat. It takes less time to use something slightly simpler and more malleable.
It has been a revelation to me to be able so see the differences in Prompt Format files so I can see what is different between styles. There were several tweaks I’d missed, and I was also able to quickly clean out duplicates and near-duplicates. I haven’t tried it in a team, but it would be nice to use it to keep an eye on the evolution of team-based formatting styles and flag up changes.
Tools in this post
You may also like
-
Article
SQL Prompt code analysis: avoid non-standard column aliases (ST002 and DEP021)
If you declare a column alias using equals SQL Prompt will raise a violation of a style rule (ST002). It's best to follow the ANSI-SQL92 standard of assigning aliases using the AS keyword. If the alias is not a standard identifier, delimit it with double quotes. using single quotes has long been deprecated (DEP021).
-
Article
How to Apply Non-Standard SQL Formatting Using SQL Prompt
SQL Prompt not only will format your code exactly as you want, but will also help you switch quickly to an alternative style, or to apply exceptions to certain parts of a SQL script, where your preferred style isn't what's required.
-
Article
Never Create Columns with ANSI_PADDING set to OFF
There is no good reason for having ANSI_PADDING set to OFF when you create tables in SQL Server. It was provided purely for legacy databases that had code that assumed the old CHAR behavior for dealing with padding, and its use has now been deprecated.
-
Article
How SQL Prompt Improves SQL Server Database Development
Everyone knows SQL Prompt for its code completion and IntelliSense features, but a lot of its extra value comes from features that allow the development team to standardize coding practices and drive up code quality.
-
