364

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
339

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
16
  • 1
    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)
    – Stephen
    Commented Jun 12, 2012 at 14:27
  • 4
    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.
    – Marco Lackovic
    Commented Apr 27, 2015 at 8:40
  • 5
    see hints for SSMS 2014 instalations stackoverflow.com/questions/23303026/…
    – Alexander Sigachov
    Commented May 14, 2015 at 15:09
  • 2
    Great tool but not compatible with 2016 RTM
    – dmeglio
    Commented Jun 15, 2016 at 17:21
  • 3
    @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)
    – Tao
    Commented Jun 1, 2018 at 7:51
213

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
  • 24
    Nice tip. Sadly, while this method does work, it does a fairly poor job imho. But at least there's something builtin.
    – John Homer
    Commented Jul 12, 2012 at 15:07
  • 2
    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 ;-)
    – Justin
    Commented Feb 27, 2013 at 2:46
  • 1
    I discovered that, similarly, queries are formatted when pasted into a new View. But, this is much cleaner... +1.
    – Feckmore
    Commented Mar 19, 2014 at 18:20
  • 24
    this will work only if the contents of the query can able to represent graphically.
    – kbvishnu
    Commented Dec 17, 2014 at 13:12
  • 6
    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.)
    – Mike Nakis
    Commented Jan 4, 2017 at 15:04
29

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
9
  • 3
    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.
    – HalvarF
    Commented Aug 10, 2021 at 8:41
  • 1
    Yes. As @HalvarF said, this is a great piece of software, but horribly named.
    – dgo
    Commented Jan 5, 2022 at 13:55
  • It still has few issues like: constantly disconnecting servers with AAD auth or missing 'view dependencies' functionality. I've tried using it for a week in work, but SSMS is still better for me (even only for queries).
    – Mr Patience
    Commented Mar 22, 2022 at 13:59
  • 1
    This is great, however, it doesn't capitalize the SQL commands.
    – Lenard Bartha
    Commented Aug 12, 2022 at 17:32
  • 1
    @LenardBartha Yes it does. You need to configure it in settings.
    – AntikM
    Commented May 13 at 11:39
6

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
2

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
1

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.