372

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"

Improve this question
4

6 Answers 6

Reset to default
345

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 :)

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Just tried it out, the options settings made this a real winner, and I only tried the website one (work restricts downloads but I'll be trying this out at home later)
On SQL server 2012 the default Ctrl+K, Ctrl+F shortcut doesn't work as it says it is already bound to Format Selection. It worked after changing it from DataWarehouse Designer::Ctrl+k, Ctrl+f to Global::Ctrl+k, Ctrl+j.
see hints for SSMS 2014 instalations stackoverflow.com/questions/23303026/…
Great tool but not compatible with 2016 RTM
@Jordan: in Windows 10 the .Net Framework 3.5 SP1 (including .Net 2.0) is an optional feature of windows, that can be enabled in the "Turn Windows features on or off" screen/functionality. It's called ".NET Framework 3.5 (includes .NET 2.0 and 3.0)", and it's right at the top of the list of optional features - does this not work for you? (yes, sorry, requiring .Net 2.0 in this day & age is probably not reasonable... Created Issue github.com/TaoK/PoorMansTSqlFormatter/issues/199 to track)
|
215

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.
Improve this answer

8 Comments

Nice tip. Sadly, while this method does work, it does a fairly poor job imho. But at least there's something builtin.
Well, as far as I can tell, although it does not really do the best job, it does something, and its out-of-the-box, and you don't have to pay for it. But only one person thought to tick it up as Useful. Im amazed really, I thought I had this one pegged ;-)
I discovered that, similarly, queries are formatted when pasted into a new View. But, this is much cleaner... +1.
this will work only if the contents of the query can able to represent graphically.
CAUTION not only it will not work for complex queries, (which are the ones that need formatting,) but also, if you select a complex query and hit Ctrl+Shift+Q under Management Studio v 11.0.6020 it CRASHES, possibly loosing some of your work. (despite the recover function.)
|
33

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.

Improve this answer

11 Comments

Just adding for clarity that Azure Data Studio is a local tool that works for all databases, local, on prem and in the cloud, its name is misleading.
Yes. As @HalvarF said, this is a great piece of software, but horribly named.
This is great, however, it doesn't capitalize the SQL commands.
For those who don't know, Azure Data Studio is basically the Visual Studio Code of SSMS. You should be able to tell at a glance, and the preferences work the same way, with a JSON file.
@LenardBartha Yes it does. You need to configure it in settings.
|
7

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
Improve this answer

Comments

7

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.

Improve this answer

Comments

3

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.