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 to the rescue with tab coloring
When you’re hard at work, it’s all too easy to make a mistake as you rush to get a job done. Say, for example, you have a QA database you need to get rid of. You run a query to drop it and then… oh no! You’ve accidentally run it in Production. Your day just
-
Article
The Sins of SELECT * (BP005)
If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table.
-
Article
Quickly rename variables all at once with SQL Prompt
Grant Fritchey demonstrates a quick and easy way to rename all variables in a SQL script.
-
Article
Finding code smells using SQL Prompt: old-style join syntax (ST001)
There are no advantages to using old-style join syntax. If SQL prompt identifies its use in legacy code, then rewriting the statements to use ANSI-standard join syntax will simplify and improve the code.
-
