How to style your SQL using SQL Prompt’s new formatting options

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

Here, you’ll see a list of available styles. We’ve included some examples to get you started, and if you set up formatting options in a previous version of SQL Prompt, you’ll see your old styles imported here too.

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.

You’re then presented with the complete range of options available to you with our new formatting editor.

---

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.

For any numerical fields, such as Number of spaces in tabs, you can choose a number from the drop-down list of common options, or simply click on the field and enter whichever number you like.

---

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.

---

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.

---

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.

---

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.

If you’re not yet a SQL Prompt user, download a free trial and see how much more productive you can be writing code every day.

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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more