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
