Formatting Code in ADS with SQL Prompt

One of the most popular and widely used features of SQL Prompt is formatting code. The SQL Prompt extension is in public preview and you can get it here. I have another post that walks through the  installation.

Once it’s installed, you will likely want to use it, and one of the things you can do is format code. This post looks at a few of the basics for formatting code.

Quick Format

Let’s see how this works. First, we open ADS and choose a connection from the Server pane. I right click a database and choose “New Query”(shown here).

2020-05-13 12_05_06-Data Analysis - Azure Data Studio

Next, I’ll enter the query here into the pane:

SELECT DISTINCT Name
FROM Production.Product WHERE ProductModelID IN (SELECT ProductModelID
                                                 from Production.ProductModel
                                                 where Name LIKE 'Long-Sleeve Logo Jersey%');
GO

Once I have this, you can see in the image that the subquery has some  poor formatting. The subquery is quite wide and some keywords are cased differently.

Query shown in ADS

Let’s CTRL+Shift+P to get the command palette. If I type format, I’ll see some options in the image below for formatting (or something similar).

command palette with format options

I’ve been testing, so the recently used commands show the format document commands, which you will often use. In this case, I’ll select Format Document with. When I do that, I’ll get a list of formatters for SQL code installed on my system. In my case, I have SQL Prompt, the default mssql formatter, and a PostgreSQL formatter.

Installed formatters in ADS

Once I select Prompt, formatting will begin. I see a note in the lower right corner of ADS.

2020-05-13 12_13_58-● SQLQuery_1 - ._SQL2017.AdventureWorks2017 (Integrated) - Data Analysis - Azure

When this is done, I can see my query is formatted appropriately (according to my style). The keywords are proper cased and the spacing for the subquery has changed.

Reformatted code

Setting a Default Formatter

I don’t want a two step process every time I need to format code. In fact, the Format Document shortcut is Shift+Alt+F, and I want to use that. In order to do that, I’ll need to configure a default format.

Note: If you haven’t and you click Shift+Alt+F, ADS will ask you to configure one.

The command palette (CTRL+Shift+P) is the way to configure ADS. Click that and type “format”. You will see a list of commands, and you want to choose the “Format Document with” option. This brings up the list of formatters, as we saw above, but below this is the “Configure” option.

Configure formatter option in command palette

We choose this, and again see a list of formatters, but the text at the top has changed. In the image we can see this now says: “Select a default formatter for ‘SQL’ files”. We can pick SQL Prompt to set that.

formatter choices

Once we’ve done this, Shift+ALT+F will format your query.

Changing Styles

One of the things many of us want to do is use a custom formatting style. While the built in styles are handy, many users want custom ones. That includes me. I have a few custom styles I use regularly in SSMS. If you run ADS on Windows, your styles are imported. If not, I’ll do another post for that.

I can change styles in the command palette. Type “SQL Prompt” in there and you see options:

SQL Prompt Options in ADS

The one I’ll use is the recently used one “Change active formatting style” at the top. If I pick that, I see this on my ADS install:

SQL Prompt Formatting Styles

There are a number of Built In styles that come with Prompt, but we can see my three custom ones have been imported from SSMS. Each time ADS starts, it imports your styles, so if you change on in SSMS, which has a better style editor, you will see it here when ADS reloads.

I will set my “way0utwest” style as active and then reformat the query. I now see this:

new query formatting

The parenthesis location has changed. I also see the notice in the lower right that appeared after selecting a new active style.

Summary

This is just a basic look at using the formatting and choosing a style. If you are on Windows, all of your styles are available. If not, you’ll have to use the built in ones or edit one for yourself. I’ll cover editing styles in another post.

I also have a video here of the process of formatting code: https://www.youtube.com/watch?v=EWCk7wUIL_s&feature=youtu.be

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.