CODEWITHSUNDEEP
sql-serverformattingssms
lomaxx
lomaxx

Reputation: 115763

Format SQL in SQL Server Management Studio

In Visual Studio & other IDEs, you can easily auto format your code with a keyboard shortcut, through the menu, or automatically as you type.

I was wondering if there is yet a way to enable this standard feature in SQL Server Management Studio?

I'm working with some large-ish stored procs that are a mangled mess of poorly formatted SQL and it'd be nice if I could just go "Select All -> Format SQL"

Upvotes: 368

Views: 557467

Answers (6)

me.at.coding
me.at.coding

Reputation: 17604

Azure Data Studio - free and from Microsoft - offers automatic formatting (ctrl + shift + p while editing -> format document). More information about Azure Data Studio here.

While this is not SSMS, it's great for writing queries, free and an official product from Microsoft. It's even cross-platform. Short story: Just switch to Azure Data Studio to write your queries!

Beginning with SQL Server Management Studio (SSMS) 18.7, Azure Data Studio is automatically installed alongside SSMS.

Source

Update: Actually Azure Data Studio is in some way the recommended tool by Microsoft for writing queries (source)

Use Azure Data Studio if you: [..] Are mostly editing or executing queries.

Upvotes: 32

questionto42
questionto42

Reputation: 9512

It should be made clearer already in the accepted answer (and not just in this new answer from 2022) that SSMS does not have a built-in formatting tool for the mere editor window in 2024 (tested in v19.3).

Saving as a view does not format the SQL code

Even if you save the query in a view and then "right click -> Script View as --> CREATE To", you will not get it formatted but keep the same indentation and case:

enter image description here

The only full-scale built-in formatter

The only built-in helper is the "query designer" answer above which seems to have a default that sadly does not put a line break after each column. You could begin with that and then try reformatting it with RegEx, but then you are faster with some other tools as in the other answers.

Small-scale built-in formatter: Change the case

There is a small built-in formatting helper left, see Manage Code Formatting -> Converting Text to Upper and Lower Case - Microsoft Learn:

To convert text to uppercase, press CTRL+SHIFT+U, or click Make Uppercase on the Advanced submenu of the Edit menu.

To convert text to lowercase, press CTRL+SHIFT+L, or click Make Lowercase on the Advanced submenu of the Edit menu.

Indentation (does not help)

The same link also shows you how to change the default indentation at To automatically indent all of your code - Microsoft Learn:

On the Tools menu, click Options.

Click Text Editor.

Click All Languages.

Click Tabs.

Click Smart.

enter image description here

Click "Smart" and "OK":

enter image description here

Yet, this tool does not change the marked SQL code's indentation and is therefore not a tool for reformatting.

PS: highly upvoted answer above does not make clear that its Add-in is outdated

The "Poor Man's T-SQL Formatter" as an Add-in for SSMS in the strongly upvoted answer above is only for SSMS until v18 while in 2024, you will have at v19 or above.

Upvotes: 3

Geoff
Geoff

Reputation: 8850

I've had very good results with Devart's SQL Complete tool. The free tier did most of what I wanted for a long time, and I eventually ended up buying it for some of the paid features (especially generating join clauses).

Saves a lot of time and it's particularly nice to be able to define a standard formatting style and save/share it with a team.

Upvotes: 2

S Nash
S Nash

Reputation: 2499

While for some reason Microsoft ignores to implement this in SSMS I found the following site which does a pretty good job of formatting the SQL Code:

https://www.red-gate.com/website/sql-formatter

Also

https://codebeautify.org/sqlformatter

Even though this one is also fairly nice, but the code it produces is a bit too verbose for me so my favorite is

 red-gate.com

Upvotes: 6

Tao
Tao

Reputation: 13996

Late answer, but hopefully worthwhile: The Poor Man's T-SQL Formatter is an open-source (free) T-SQL formatter with complete T-SQL batch/script support (any DDL, any DML), SSMS Plugin, command-line bulk formatter, and other options.

It's available for immediate/online use at http://poorsql.com, and just today graduated to "version 1.0" (it was in beta version for a few months), having just acquired support for MERGE statements, OUTPUT clauses, and other finicky stuff.

The SSMS Add-in allows you to set your own hotkey (default is Ctrl-K, Ctrl-F, to match Visual Studio), and formats the entire script or just the code you have selected/highlighted, if any. Output formatting is customizable.

In SSMS 2008 it combines nicely with the built-in intelli-sense, effectively providing more-or-less the same base functionality as Red Gate's SQL Prompt (SQL Prompt does, of course, have extra stuff, like snippets, quick object scripting, etc).

Feedback/feature requests are more than welcome, please give it a whirl if you get the chance!

Disclosure: This is probably obvious already but I wrote this library/tool/site, so this answer is also shameless self-promotion :)

Upvotes: 343

Justin
Justin

Reputation: 3443

There is a special trick I discovered by accident.

  1. Select the query you wish to format.
  2. Ctrl+Shift+Q (This will open your query in the query designer)
  3. Then just go OK Voila! Query designer will format your query for you. Caveat is that you can only do this for statements and not procedural code, but its better than nothing.

Upvotes: 216

Related Questions