Over
the past few blog entries, I’ve been looking at parsing TSQL scripts in a variety of ways for a variety of tasks. In my last entry ‘How to prevent ‘Select *’ :
The elegant way’, I looked at parsing SQL to report upon uses of SELECT *.
The obvious question leading on from this is, “Great, what about other
code smells ?” Well, using
the language service parser to do that was turning out to be a bit of a hard job, sure I was getting tokens but no real context. I wasn't even being told when an end of statement had been reached. One of
the other parsing options available from Microsoft is exposed in
the assembly ‘Microsoft.SqlServer.TransactSql.ScriptDom’, this is ,I believe, installed with
the client development tools with SQLServer. It is much more feature rich than
the original parser I had used and breaks a TSQL script into intuitive classes for analysis. So, what sort of smells can I now find using it ? Well, for an opening gambit quite a nice little list. Use of NOLOCK Set of READ UNCOMMITTED Use of SELECT * Insert without column references Explicit datatype conversion on Sargs Cross server selects Non use of two-part naming convention Table and Query hint usage Changes in set options Use of single line comments Use of ordinal column positions in ORDER BY clause Now, lets not argue
the point that “It depends” as smells on some of these, but as an academic exercise it is quite interesting.
The code is available from this link :https://www.dropbox.com/s/rfk32sou4fzl2cw/TSQLDomTest.zip All
the usual disclaimers apply to this
code, I cannot be held responsible for anything ranging from mild annoyance through to universe destruction due to
the use of this
code or examples.
The zip file contains a powershell script and my test cases.
The assembly used requires .Net 4 to run, which means that you will need powershell 3 ( though im running through PowerGUI and all works ok ) .
The code searches for all .sql files in
the folder hierarchy for
the workingpath, you can override this if you want by simply changing
the $Folder variable, and processes each in turn for
the smells. Feedback is not great at
the moment, all it does is output to an xml file (Smells.xml)
the offset position and a description of
the smell found. Right now, I am interested in your feedback. What do you think ? Is this (or should it be) more than an academic exercise ? Can tooling such as this be used as some form of
code quality measure ? Does it Work ? Do you have a case listed above which is not being reported ? Do you have a case that you would love to be reported ? Let me know , please mailto:
[email protected]. Thanks
