Product articles SQL Prompt SQL Formatting and Styles
How to Compare Two SQL Prompt…

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.

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.

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.

Comparing SQL formatting options

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.

A SQL style file in the JSON format

Unless you have changed the path of the Style folder to access a shared drive, these styles are stored here:

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.

You can use it like this

You can filter for just those whose values had changed

Or those with values only in the target

…and so on.

You can specify the objects you don’t want to investigate for the comparison

You can export the result as a CSV or anything else you like

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

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more