Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Comments

Posted by Ed Klein on 27 June 2011

I also use RedGate SQL Prompt, but before purchasing it, or when working on a system where it has not been liscensed, I have found the following online SQL formatting tool to be very good.  

http://www.sqlinform.com  

Posted by james-312924 on 3 July 2011

Free online SQL formatter without any query size limitation:

www.dpriver.com/.../sqlformat.htm

Posted by stewart.locklear on 22 November 2011

This really works great.  It also works for Netezza SQL for which there aren't any good formatters.  

Leave a Comment

Please register or log in to leave a comment.