Wish I'd discovered this years ago

One of the tasks I'm most frequently confronted with is updating or converting database queries. These are very often formatted as a fairly indigestible single block of text with no line breaks, just one solid word-wrapped block of code. This is mostly the result of people using WYSIWYG query editors where the underlying query code is invisible. The first thing I do, then, is to try to tidy up the code to make it readable and get a sense of what the query is  supposed to accomplish, and how. This involves, among other things,  inserting line breaks so that each field gets its own line, and formatting joins so that the joined tables and columns are distinct and easily readable. As you can imagine, this can be an tedious process, particularly if you're doing it a lot.

After spending most of a day last week doing nothing else but tidying up and converting Access queries to SQL views, I got fed up with doing it by hand and did a bit of Googling, and discovered this brilliant trick. In SQL Management Studio (and any other Visual Studio-based editor) you can use the Find and Replace dialog to replace any character with line breaks. Simply tick the Use check box and select the "Regular Expressions" option, then use "\n" as the replacement string. In the example below, I replace each comma (which occur after each field name in a query SELECT list) with comma + line break (", \n"), click Replace All and half the work in reformatting my query is done. Replacing with tab characters is simply a case of using "\t" instead of "\n" (so replacing "JOIN " with "JOIN \t" begins tidying up the table join clauses).

It's obviously possible to do much more sophisticated replace actions with this capability (there's a good list here), but this is already a life-saver. Hallelujah!