1 June 2017
1 June 2017

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

SQL Prompt formatting 1
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.

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


Global options

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.

SQL Prompt formatting 3
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.

SQL Prompt formatting 4


Statement options

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.

SQL Prompt formatting 5


Clause options

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.

SQL Prompt formatting 6


Expression options

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.

SQL Prompt formatting 7


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.

SQL Prompt formatting 8
 

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.

SQL Prompt formatting 9


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.

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 in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

Related posts

Also in Blog

Down Tools Week 2017

Once a year, we hold Down Tools Week at Redgate. It’s our version of a hack week – a chance to put down the day job and spend a week working on something completely different. It’s an opportuni...

Also in Redgate products

SQL Monitor plans for the second half of 2017

SQL Monitor's dev team has made huge improvements to the product over the last year. In the first half of 2017 alone, they released reporting capabilities, support for collecting metrics from VMWare h...

Also in Software development

How might classification and better documentation improve data safety?

In this post, we imagine how auto-classification of data can be used to build better documentation that helps you trust that your organization can use data without posing a risk or compromising regu...

Also about SQL Prompt

The Louis Davidson custom style for SQL Prompt

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