How to Put SQL Column Names Onto Multiple Lines in SSMS

SQL in 60 Seconds #1

A few keystrokes and BAM! A mostly formatted query

SQL in 60 Seconds is a series where I share SQL tips and tricks that you can learn and start using in less than a minute.

Have you ever copied and pasted a query into SQL Server Management Studio and been annoyed that the list of column names in the SELECT statement were all on one line?

There are 30 columns here. Ugh.

You can make the query easier to read by putting each column name onto its own line.

Simply open the Find and Replace window (CTRL + H) and type in ,(:Wh)* for the Find value and ,nt for the Replace value (in some versions of SSMS you may have better luck using ,(:Wh|t| )* in the Find field). Make sure “Use Regular Expressions” is checked and press Replace All:

Make sure the regular expression icon/box is checked
A few keystrokes and BAM! A mostly formatted query

The magic you just used is a Regular Expression, and Microsoft has its own flavor used in SSMS and Visual Studio. Basically, we found text that

  • began with a comma (,)
  • followed by any whitespace (:Wh) (line break, tab, space, etc…)
  • (in newer versions of SSMS we add |t| to indicate or tab or space)
  • and replaced it with a comma (,) and a new line (n) and tab (t).

Sure, this trick isn’t going to give you the same output as if you used a proper SQL formatter, but this technique is free and built straight into SSMS.

 

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Leave a Reply

Your email address will not be published. Required fields are marked *