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 on codeplex http://tsqlsmells.codeplex.com/ 

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: parser@clearskysql.co.uk.

Thanks