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