Format SQL in SQL Server Management Studio

sql-server formatting ssms

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"

SQLinForm provides a Hotkey feature

it has now a free online formatter: sqlinform.com/sql_formatter_online.html

Online you can use devtoolsonline20190908040816.azurewebsites.net/DevTools/…

Updated Q to be on-topic

Hp_issei

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

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)

Cœur

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.

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

stefan.at.wpf

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!

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.

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.

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

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

Format SQL in SQL Server Management Studio

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Links

Contact US