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

How advanced is database DevOps in financial services?

Whether you’re exploring the advantages of DevOps or already fully immersed in the journey, including the database brings additional advantages. But how are you performing compared to the competitio...

Also in Database development

Why it's time to think seriously about SQL Server 2017

SQL Server 2017 officially landed today and is now on general release. The latest version of the heavyweight platform is more than the sum of its parts, however, because it doesn’t just deliver new ...

Also about SQL Prompt

A day in the life of a developer with SQL Prompt

I've a a busy day ahead of me, working on a new reporting query, plus reviewing some initial database designs for the team. We've done the morning stand-up meeting, I've had my morning coffee, SQL Ser...