What?

Simply put, it is possible to include checks in the SSDT (Sql Server Data Tools) build process - you come up with a set of rules or start with these (https://tsqlsmellsssdt.codeplex.com/) and when you check-in your code, as part of your normal build or CI build you run the code analysis - the code analysis verifies that the code is good and either continues the build or fails it if you have committed a crime against your rules.

Why?

Code quality is important, one part of a quality codebase is consistency and standards and if you don’t enforce them, how do you know the standards are being followed? The quick answer is that you don’t, and you don’t have to either, just decide on the rules and enforce them and then go on worrying about something else (like how to deploy data as part of an SSDT deployment!)

How?

The first step is to work out what rules you want to enforce and then you need to implement the code to enforce them, sounds easy right?

There are a couple of approaches,the simplest would be to get the code smells extension from https://tsqlsmellsssdt.codeplex.com/ and either use that as it is or extend the rules, it is open source so you can and should contribute back any additions you make to it.

If you want to write the rules yourself then you can start with the DacFx samples from Microsoft: https://dacsamples.codeplex.com/SourceControl/latest#RuleSamples/Capital…

Once you have the rules and have compiled them (they are C#) you end up with a dll you need to copy into the Dac folder, if you are using Visual Studio 2013 and the November 2014 SSDT release then the extension folder is:

C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\120

I will put up a seperate blog about where to find the extensions folder for each release as it does float around a little!

Once you have the dll in place, restart visual studio to pick up the new dll and then right click on the solution that contains the SSDT project (don’t click on the project, click on the solution) and then choose “Run Code Analysis On Solution”:

To run the code analysis everytime you build the project you can set the project option “Enable Code Analysis on Build”:

To run the code analyis from the command line on a specific project run:

msbuild /t:StaticCodeAnalysis project_name.sqlproj

Done

Hopefully this has piqued someones interest enough to start checking the quality of their code as part of their build process, writing the rules themselves is a little tricky but there are enough samples around to get you started and hopefully more people will start sharing what it is they do so we can all benefit in this extremely important development activity.