At SQLBits I had a number of conversations with a number of people over TSQL Smells, my open source project for static code analysis of SQL Code. The general opinion was that although the concept was sound, the process of getting it running ( and developing it further ) was not documented. Im sure that if you have sufficient .Net skills you could muddle through on your own, but since it will be mainly TSQL devs who have the interest here, I thought I would take the time to draw up some ‘Arse and Elbow’ documentation to get you started.

So lets go :

1. Grab Visual Studio 2013. Community edition is fine operationally ( Im not a lawyer, check the licensing terms).
2. After that has installed, update SSDT. Tools -> Extensions and Updates -> Updates -> Product Updates
3. Install both the 32Bit and 64Bit versions of the DACFX framework, at time of writing latest is downloadable from http://www.microsoft.com/en-us/download/confirmation.aspx?id=45886
4. Ensure that the SSDT extensions directory ( C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120\Extensions ) is read writable ( I use Everyone / Full control ).

That should be the tooling side of the equation sorted.

Now clone ( I wont go into what this means , a number of tutorials are available on github) the TSQL Smells repo on github http://github.com/davebally/TSQL-Smells

In terms of software this should now all be done.

Run up visual studio, load in the TSQLSmells solutions and “Rebuild Solution”.

If that all goes according to plan, you will now be able to run the unit tests

And if you are even more lucky they will all pass.

This now means that the rules are installed correctly and we can proceed onwards.

The unit test cases reference sql files that are held in the TSQLSmellsTest project, you can further test by loading this project and selecting the “Run Code Analysis” option.

You are now in a position to load your own projects and run the code analysis against them.

Finally, included in the solution is RunSCAAnalysis, this generates a small exe that can run the static code analysis rules against a database or dacpac.

Command line options for that are –S Server –o outputfile and one of –d
database or –f filename, it would be my intention with this that it that it would form part of your automated build/deploy framework (if you are not using SSDT) and break the build on a “Smell”.

I hope that this blog entry goes someway to starting you on your way to better TSQL Code quality and for my part, I will be giving it some love in the near future.

Update 16-apr-2016

Ive had some comments that the project is not working so ive now update the GitHub project to work with VS2015 / SQL 2014.  To do this i built a fresh azure VM with VS2015 installed and i did run into some problems.

If you are having build problems :

Mostly these may be due to missing references and due to the multiple packages involved it hard to give absolute instructions.

Missing Dac references : You need to find and install the latest ( or appropriate) DACFX packages (32Bit is essential , 64 Bit is optional for a runtime env).  The SSDT team blog is the best source of reference https://blogs.msdn.microsoft.com/ssdt/ After install you may need to delete the reference in the project , add reference and browse to the DACFX folder ( C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\ ) and add the dlls in.

If you are not using VS2015 & SQL 2014 then you will probably have to look in different version number folders.

Missing TransactSql.ScriptDom reference : This is installed as part of sql server and not vs,  if you do not have SQL Installed, you will need to find “Transact-SQL Language Service” from the SQL Server Feature pack https://www.microsoft.com/en-gb/download/details.aspx?id=42295.  The package is tsqllanguageservice.msi.

Again delete the references and re-add by browsing to the folder, C:\Program Files (x86)\Microsoft SQL Server\130\SDK\Assemblies.  Again the 130 version number will change over time / older versions.

It builds but the tests fail :

This will probably be due to the output being placed in the incorrect folder.  SSDT will look to the folders (C:\Program Files\Microsoft SQL Server\X\Dac\Bin\Extensions\ ) where X is the SQL Version being targeted..  Make sure that the folder exists for your specific setup.

I wish that there were a way to get over all the versioning and streamline this but as of yet there isnt.