The ability to customize and refine formatting options has long been a feature of SQL Prompt. In the eighth major update to the tool, we’ve introduced a brand new formatting engine that lets you manage your formatting options like never before.
Not only can you set your style exactly how you like it, with a vast range of options for whitespace, casing, statements, clauses, expressions and more, you can also save multiple styles and quickly switch between them.
In this #SuperSQLTip, we’re going to show you how to create your own formatting style using the new options in SQL Prompt 8.
This tip uses SQL Prompt in SQL Server Management Studio (SSMS), and exactly the same functionality is available in Visual Studio too.
To get started, go to the SQL Prompt menu and choose Edit Formatting Styles… (you can also find this under SQL Prompt > Options > Styles or by right-clicking and choosing Active Style > Edit Styles…).
When you create your style, you can choose one of your existing styles to base it on, so you don’t have to start from scratch.
Within Global options you can choose how whitespace gets displayed, such as the number of spaces in a tab, line spacing and wrapping. You can also choose how your lists are aligned and indented, where parentheses are placed and how you want the casing to appear.
Here, you can refine how your statements appear. For example, how your clauses align, the parentheses for CREATE and ALTER statements, how control flow statements appear, and the alignment of variables.
You can set the alignment and position of JOIN clauses and ON conditions in this section, such as right-alignment, empty line inserts and keyword alignment to tables.
This final section lets you set how function calls are displayed, set your CASE options such as placing WHEN and THEN expressions on new lines, the alignment of IN statements, and the positioning of operators.
See your changes live in the preview box
As you set up your options, you’ll see a preview box underneath that shows exactly what impact the changes will have on your code. You can choose to use our demo script or preview your current query live. This way, you can keep tweaking your style until you have it exactly how you like it.
When you’re happy with how everything looks, simply click Save and your new style will be ready to use.
Save multiple styles
You can create and save as many styles as you need. You’ll find them under the Your Styles section in the Formatting styles screen. From here, you can also set your default Active style.
This is particularly useful if you have your own preferred style you like to work in, but your company has a preferred format they like to use.
It means you can write your code in your style, and then right-click and change your Active style to your company style. Click Format SQL or press Ctrl+K, Ctrl+Y and your code is instantly switched to that style.
Start creating your own styles
The new SQL Prompt 8 is available right now, and we hope you like these exciting new features. If you have any feedback, please get in touch with the team via the SQL Prompt forum or the UserVoice page. And to see it in action, watch the latest tips video.
Like this? Share it with other SQL professionals using the social sharing buttons below. Simply tweet: I’ve just read @redgate’s blog on how to set up formatting in the new version of #SQLPrompt – see the #SuperSQLTip at http://rd.gt/2raT7Rl.
Also in Blog
In this three-part series, guest bloggers from DevOpsGuys look at the real role of Ops in DevOps. Where it changes, how it changes, and why Ops has an important part to play in the brave new world of ...
Also in Redgate products
My previous article in this series explained why it's important for a development team to adopt a common standard for formatting SQL code. It also gave a broad overview of the styles and actions withi...
Also in Software development
A data discovery and classification research project from Foundry
In Foundry, we’re responsible for developing new products and technology to support the changing needs of our customers. We’ve ...
Also about SQL Prompt
With ten programmers working on the same project, how do you agree on a standard style for formatting SQL code, and then implement it consistently? One way is through draconian rules, meeting after me...