I learned a very useful T-SQL script formatting trick today. I often talk about formatting and commenting for the “person from mars” which is usually “you” coming back to look at your code 3 years from now having forgotten everything you ever knew about this project and learned better ways to do everything that was done in this project but now you just need to make a “quick” update. How do you find the one piece of code in the thousands of lines of code (perhaps millions) that make up this solution? Well hopefully you left yourself a few clues AKA comments that call out important lines or sections of code and hopefully the code has been formatted in a way that makes it easy to read and debug. Well developers cannot survive on google searches and hope alone! We need to help our future selves out a little. So let’s take a look at a formatting trick that will make your T-SQL statements easier to read and debug. When creating stored procedures, views and user defined functions you may need to comment out one or more lines for debugging or testing reasons, however when all of your code is on a single line it makes it very difficult to comment out a single column reference or where clause. So while debugging stored procedures I used to spend a lot of time (before I learned this new trick) using a keyboard shortcut. The keyboard short I had been using was “Ctrl + Right Arrow” this keyboard short cut skips to the beginning of the next word (commas count as words so it will stop at every comma as well) so in a statement such as: “Select FirstName, LastName, Phone, EmailAddress from Person.Person” the keyboard shortcut will move us from the beginning of the word Select to the beginning of the word FirstName the next time we press the arrow key we will move from the begging of FirstName to just before the comma (after FirstName). Using this short cut the key patter would be Ctrl + Right Arrow 2x (press the right arrow twice while holding down the Ctrl key) then Press Enter (to move “, FirstName” to the next line) then Ctrl + Right Arrow 2x, Enter (to move “, LastName” to the next line). Repeat for each column in the table. This works and I’ve been using it for years but there is a better way! The New Way… Using Search and Replace we can replace all commas with a carriage return and a comma.
Ctrl + H opens the search and replace window.
In the find what box type a comma.
In the Replace with box type \n,
then in Find Options select the “Use:” checkbox
then choose Regular Expressions from the dropdown.
This will replace all commas with a carriage return and a comma.
Converts this: Select FirstName, LastName, Phone, EmailAddress from Person.Person
To this: SelectFirstName, LastName, Phone, EmailAddressfrom Person.Person
Thanks for the great tip, Cal!
