How to Format Your Queries in SSMS
Microsoft SQL Server Management Studio 22.7 added the ability to format your T-SQL queries by right-clicking and clicking Format SQL, or by hitting control-K, control-Q on your keyboard:
You’re probably not going to like the default formatting out of the box because you’re the kind of person who has strong, passionate feelings and likes to argue about them on the Internet. Good news! The configurations are really flexible. Go into Tools, Options, SQL Formatter:
Keep scrolling:
So much control over what gets put into new lines:
Oh you thought we were done with the new line stuff? No no, homey:
These options are perfect for control freaks like you, my friend. After you’ve got your formatting just the way you like it, click the “Edit user settings as json” at the top of the settings list, and you can copy/your defaults out to save them, pass them around, and trade them with your friends.
To get on the latest SSMS, click Help, Check for Updates. (If your SSMS is so old that it doesn’t have Check for Updates, download it from SQLServerUpdates.com.) After SSMS updates itself and relaunches, you’ll be presented with a slick What’s New screen summarizing the most recent changes, with a table of contents on the left side:
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
This is a great improvement! Almost everyone has their preferences, and what I did not like in particular is that every JOIN is formatted as 3 lines minimum. Would be nice to have some options for that considering all the other options they made. Yes, I’ll add some feedback to MS instead of complaining in your comment section 😉
You’re right, Brent.
We are an ornery bunch of critters, all with our own opinions and standards.
Like I want JOIN as two lines.
And a gutter at column 7
With keywords right-aligned to column 6 and the rest of the clause at column 8.
For me it’s about prevention of mistakes, and ease of checking.
Are the defaults exactly what I like? No.
Are they far, far better than what I am used to seeing from my developers? Yes!
I can’t wait until comma-control arrives. When I first started writing SQL code about twenty years ago I HATED prefixed commas. Now, I can’t stand suffixed commas. We can change!
At another company I had a license to Regdate’s SQL Prompt and really liked its customizability.
Not a fan as it is – yet. In fact I would prefer not to format the code shown in the animated GIF since it would take less time to format the code the way I like it, then to alter the formatted code to my taste.
In Schema Compare (Preview) in SSMS the procedures and functions are shown side by side, not as it is, but ‘formatted’.
You read that you can change the settings to suit you though, right? And then from that point on it’s only two keystrokes to format code.
I did read that, sir. But I do miss quite a few options to bring it close to the desired end result. As others pointed out, the JOINs take multiple lines whereas I – strongly – prefer just one. And I would love to see more indentation options.
OK cool, just making sure. I agree, it’s not perfect, but it’s so much better than hand-formatting stuff, and it’s built into SSMS so I don’t have to ask clients to pay for or install anything. I can at least just say, “Set this up the way you like it before we work together, and then my code will match your standards – or just reformat it when I send you stuff.”