Format SQL in SQL Server Management Studio

Asked 31/12, 2008 at 0:28 Answered 22/2 at 16:35

357

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"

Formal answered 31/12, 2008 at 0:28 Comment(4)

SQLinForm provides a Hotkey feature – Kinesics 15/9, 2015 at 21:48

it has now a free online formatter: sqlinform.com/sql_formatter_online.html – Kinesics 26/9, 2015 at 20:57

Online you can use devtoolsonline20190908040816.azurewebsites.net/DevTools/… – Humoral 10/9, 2019 at 15:8

Updated Q to be on-topic – Prefigure 13/2, 2020 at 15:33

338

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

Thompkins answered 25/8, 2011 at 21:36 Comment(13)

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) – Chickenhearted 12/6, 2012 at 14:27

And it works even for SSMS2012, what is awesome! You just have to restart SSMS. – Breadstuff 4/3, 2014 at 8:55

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. – Snakebird 27/4, 2015 at 8:40

see hints for SSMS 2014 instalations #23303526 – Misstep 14/5, 2015 at 15:9

Adding on to Kirge's comment, be sure to search the Command list for "PoorMansTSqlFormatterSSMSAdIn" instead of just "Format", it doesn't want to show up with just "Format" for SSMS2014 when I type it. Change that one to have a (Global) binding instead of the "Edit.FormatSelection" version. – Krause 3/5, 2016 at 19:31

Great tool but not compatible with 2016 RTM – Impeach 15/6, 2016 at 17:21

could you please update your answer, the PoorMan SQL Formatter plugin doesn't work with SSMS 2016 anymore. – Bayern 21/7, 2017 at 13:44

@dman2306 - installer compatible with 2016 (and 17) now available. – Thompkins 17/10, 2017 at 21:48

Meh, when I try to install this it demands .Net 2.0 be installed which I can't separately install on a Win 10 machine. Sadness. – Romanic 31/5, 2018 at 15:27

@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) – Thompkins 1/6, 2018 at 7:51

I would say thank you and that I voted your comment useful, but stack overflow doesn't let me. Let's try to do so without it realizing this. Hmm? – Romanic 8/6, 2018 at 14:50

I could not get this to work with SSMS 17.9.1. It did not seem to add the extension, it did add to VS, however I removed it because I did not want to risk keyboard collisions there. I was able to add Poor Man's to Notepad++, seems to look reasonable for the format, also has some config options that can help to format to what you prefer. – Johanson 25/4, 2019 at 17:35

I tried extension in SSMS however it errors out every time I open SSMS. I found the same exact error online however it's listed as closed and the years are 2017,2018. It's currently 2019 and I am using SSMS v18.2. github.com/TaoK/PoorMansTSqlFormatter/issues/187 – Gerigerianna 10/9, 2019 at 16:45

209

There is a special trick I discovered by accident.

Select the query you wish to format.
Ctrl+Shift+Q (This will open your query in the query designer)
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.

Gaud answered 16/5, 2012 at 23:48 Comment(8)

Nice tip. Sadly, while this method does work, it does a fairly poor job imho. But at least there's something builtin. – Zeist 12/7, 2012 at 15:7

it formatted but not the expected way. Anyway something is better than nothing :)..... Thanks for the tip. – Rail 26/2, 2013 at 21:59

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 ;-) – Gaud 27/2, 2013 at 2:46

I discovered that, similarly, queries are formatted when pasted into a new View. But, this is much cleaner... +1. – Froze 19/3, 2014 at 18:20

this will work only if the contents of the query can able to represent graphically. – Utmost 17/12, 2014 at 13:12

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.) – Harvest 4/1, 2017 at 15:4

so until i install some plugin, i can save some time. thanks. – Differential 21/3, 2017 at 3:46

Yes that is what caused the mess I'm trying to clean up. It ends the line with ISNULL and puts the parenthesis for the function on the next line. :( – Petroglyph 30/1 at 16:12

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.

Breunig answered 26/12, 2020 at 14:5 Comment(8)

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. – Rost 10/8, 2021 at 8:41

Yes. As @Rost said, this is a great piece of software, but horribly named. – Fawnfawna 5/1, 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). – Sassanid 22/3, 2022 at 13:59

I don't know why they haven't put this same functionality into SSMS, but it does work and it's better than nothing. I also found there is a convenient way to open it from SSMS, you can go to Tools >Azure Data Studio and it will open – Varicose 22/6, 2022 at 17:41

This is great, however, it doesn't capitalize the SQL commands. – Phantasmagoria 12/8, 2022 at 17:32

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. – Platinum 20/9, 2022 at 16:21

@Varicose Probably because they want it to be like VS Code, so they can reuse components of the software. – Platinum 20/9, 2022 at 17:41

Azure Data Studio is basically VSCode :) – Trichoid 23/4, 2023 at 14:37

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

Grotesque answered 12/8, 2022 at 15:58 Comment(0)

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.

Goulet answered 22/2 at 16:35 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags