Developing Custom T-SQL Static Code Analysis Rules

  • Article

Earlier this week I presented at an internal conference on the topic of Database Project Extensibility provided by the VSTSDB GDR release. 

We had requests to share the demo code and so I am posting it here.
 
The sample code contains a handful of custom static code analysis rules:

  1. Avoid WAITFOR DELAY statement in Stored Procedures – You can find the walkthrough sample on MSDN.
  2. sp_ prefix not allowed for stored procedure names – This is similar to the rule that ships with the product and shared by Gert. 
  3. Comments Required for Stored Procedures – New sample. This rule checks to make sure you have comments for all Stored Procedures in your Database Project.
  4. Primary Key Required for Tables – New sample.  Guess what it does? ;)
  5. Identity Column Named “Id” for Tables – New sample.  For folks using a surrogate primary key approach as a standard practice.


Custom Rules displayed in Code Analysis Tab of Project Properties

See the file StepsToImplementSCARule.txt in the SCAExtDemo project.  The project currently has post a build event that will copy the assembly and extension.xml file to the extensions directory. 

Gert has a great walkthrough post on how to create a custom Static Code Analysis rule.  I won’t duplicate that content here.  If you run into trouble running the samples you may want to check out his walkthrough.

You will see from the samples that creating custom static code analysis rules is a slam dunk and can support T-SQL coding standards your team may have already defined. What kind of rules do you need to implement for your team’s projects? 

Download the sample code from my Live SkyDrive

Comments

  • Anonymous
    February 25, 2014
    I have tried this for Database projects in VSTS2010. Can you let me know if this is applicable for .sqlprojects in SSDT as well?