T-SQL Formatting Workaround (Word Macro)

Hi all! I’m on vacation this week, but I’ve cleverly scheduled this blog post so you won’t miss me too terribly much. Enjoy!

I love a well-formatted SELECT statement. To that end, I often use Redgate’s SQL Prompt (with handy-dandy auto-formatting feature, just CTRL-K-Y!) to keep my code in line. But sometimes a job won’t spring for a copy, and I’ll be durned if I’m going to keep my personal key on a work box.

We’ve got the free SSMS Tools Pack that’ll do part of the job for you (CTRL-K-G). And there are a couple of free SQL formatting sites online, but I don’t feel like opening up a browser every time I need to make sense of a query. So what’s a SQL professional to do?

I made a macro. That’s right…in dire circumstances, I’ll copy-paste a query to Word, run my macro, and paste it right back into SSMS. For your enjoyment and edification, I hereby provide this macro, such as it is, to you. 

Please note:

  • You can make your own macro with the ‘record macro” button. Just paste a query in word before  you begin, and do the series of replace-alls you need to make it look like you want. This is a little time intensive, though, which is why I’m sharing my already-made macro.
  • This macro isn’t perfect, and doesn’t format all code perfectly. It’s just what I’m using at work at the moment.
  • You’re welcome.

Instructions

This is directed at Microsoft Word 2007…for other versions, do the best that you can.

  1. Click the View tab.
  2. Click the arrow under Macros, and select View Macros.
  3. Enter the name Format_SQL, and click the Create button.
  4. Paste the contents of the “Macro Code” section, below, just before the “End Sub”. 

Exhibit A: the Macro outline

Sub Format_SQL()

‘ Format_SQL Macro


<– the macro code goes here

End Sub

Macro Code

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "^p"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "^t"
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = " "
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "FROM"
.Replacement.Text = "^pFROM"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "INNER JOIN"
.Replacement.Text = "^pINNER JOIN"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "LEFT OUTER"
.Replacement.Text = "^pLEFT OUTER"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "RIGHT OUTER"
.Replacement.Text = "^pRIGHT OUTER"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "WHERE"
.Replacement.Text = "^pWHERE"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = " ON "
.Replacement.Text = "^p^tON "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "GROUP BY"
.Replacement.Text = "^pGROUP BY"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "ORDER BY"
.Replacement.Text = "^pORDER BY"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = " AND "
.Replacement.Text = "^p^tAND "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = ","
.Replacement.Text = "^p^t,"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " JOIN"
.Replacement.Text = "^pJOIN"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = "INNER^pJOIN"
.Replacement.Text = "INNER JOIN"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
With Selection.Find
.Text = "OUTER^pJOIN"
.Replacement.Text = "OUTER JOIN"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll

The End

 Hope you enjoy, and happy days!
Jen McCown
http://www.MidnightDBA.com/Jen

1 thought on “T-SQL Formatting Workaround (Word Macro)

  1. Pingback: Great Tools for Formatting T-SQL | SQL Awesomesauce

Comments are closed.