SQL Server Data Tools – Code Analysis

Last Updated on November 19, 2012 by John Morehouse

Ok, I’m a fan of having the database in some form of source control, thus I’ve been using Visual Studio to help develop databases for a number of years now.  I also know that there are vast arguments for either side of the fence but that’s for another post.  Regardless, I keep my databases contained in source control and currently use a combination of Management Studio and Visual Studio 2010 (not going to 2012 just yet) to design/create/maintain my databases.

Recently I upgraded my Visual Studio 2010 to use SQL Server Data Tools, which is an add on to Visual Studio.  It replaced what was formerly known as ‘Datadude’.  While exploring the tool, I discovered that there is a section for TSQL Code Analysis, shown below.

Click on image for full size.

Now, maybe previous versions had something similar.  I don’t recall seeing an option like this, however it’s possible that I missed it.  As you can see it checks for a number of things and will help to keep certain “standards” in check.

Update: Thanks to Jamie Thomson (T) for letting me know that Code Analysis was indeed in Data Dude in previous versions.  As stated, I just missed it!

The other beautiful thing about this is that it will warn you of an issue right in the IDE so that you can resolve the issue quickly before you ever get to the build process.

IDE Messages

I’ve checked the “Enable Code Analysis on Build” (upper left hand corner of the first image) so that every time I build the database solution the code analysis will run.  If you wanted to leave that off, you could, however by enabling this option you ensure that you’ll at least get a warning message out of the build process if it determines that there is a violation.

Here’s what the warning messages look like in the Output window.

Output Window Warnings

You can also turn on the ‘Treat Warning as Error’ (right hand column in the first image) option if you so desire, thus completely stopping the build process until the issue has been resolved.

These tools will also help those that might not claim to be full fledged DBA’s but wish to adhere to some level of database standards in their coding process.  I know that the list of options isn’t extensive, but at the very least it’s a start.

Unfortunately, at this time of this writing you can’t add to this list as SQL Server Data Tools doesn’t have any extensibility at the moment.  However, according to the MSDN forums it’s on the white board for a future release.  When the ability to add custom rules becomes available, this feature will totally rock and offer up a slick way to do an instant code review straight out of the box!

If you’re interested to learn more about Data Tools, you can find it here: http://msdn.microsoft.com/en-us/data/tools.aspx

The SSDT Team also blogs here http://blogs.msdn.com/b/ssdt/ so you can watch that space for future updates and information!

Enjoy!

© 2012, John Morehouse. All rights reserved.

13 Replies to “SQL Server Data Tools – Code Analysis”

  1. btw, you can try SqlCodeGuard or Idera SqlEnlight – they have more rules than SSDT

  2. Hi Lincoln,

    I’m looking for Code Analysis for SSIS, SSRS & SSAS projects, when I create a SSIS/SSRS & SSAS projects in Visual Studio 2015 update 2 and try configuring code analysis I get a message stating there are no analyzable projects loaded. I’m a beginner for sql, so kindly throw some light on this.
    Thank you in advance.

  3. Hi Srinidhi! I’m not sure if SSDT has code analysis for those type of projects. I’d have to look to see what is available for them. I would suspect that there is some tool somewhere that would facilitate what you’re looking to do. I could be wrong though.

  4. Howdy! Thanks for visiting! The last time I checked, no, there isn’t a way to customize the analysis rules. From my understanding that ability (along with the ability to add new ones) was supposed to be coming in SSDT but I don’t think it ever surfaced. I did find someone who created their own extension of the rules, https://github.com/ashleyglee/TSqlRules (use at your own discretion) that might help.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.