Post reply

T-SQL Code Analysis Tools

  • January 9, 2014 at 5:52 am

    #98871

    I am researching T-SQL Code Analysis tools, like the equivalent of an FxCop for T-SQL that will scan thousands of lines of stored procedures and views in a database and tell you when you're heading down a dangerous path. (SELECT *, nested views, cursors, etc.)

    Since we are looking to start fully using SQL Source Control from RedGate, this would ideally be a tool that integrates with Management Studio rather than BIDS/SSDT. We already have SQL Prompt, so there seems to be some overlap of features, but asking Grant Fritchey on Twitter yesterday it sounds like nothing from RedGate will do strict code analysis.

    It looks like if you are using Visual Studio Database Projects, you have a built-in option:

    http://msdn.microsoft.com/en-us/library/dd172133(v=vs.100).aspx

    But this tool appears to stay fully within the SSMS environment:

    http://www.ubitsoft.com/products/sqlenlight/index.php

    Is anyone aware of any others, or has any experiences to share when doing Code Analysis in T-SQL?

  • January 10, 2014 at 7:18 am

    #1679158

    Thanks to last night's DC PASS meeting I have learned there's at least another option:

    SQL Cop: http://sqlcop.lessthandot.com/detectedissues.php

    But it sounds like not many people are doing this. I thought I would document this in case anyone else in the future is searching for info on code analysis.

  • January 23, 2014 at 8:41 am

    #1682834

    I have just downloaded SQLEnlight and started evaluating it and would be interested to hear any other comments about this product. My intention is to use it primarily for static code analysis (my role is one of QA).

    My initial impressions:

    1) There are spelling mistakes all over the error messages - this creates a poor first impression.

    2) Analyses of DBs is quite a bit slower than analyses of .sql files.

    3) Building your own rules looks a bit complex (though I haven't yet tried too hard, maybe I'm wrong).

    4) The XML output report takes a bit of time to get to grips with (but I've managed to use QlikView to make some sense of it).

    5) The command-line options offer great potential for continuous integration - just need to work out exactly which rules are most important and how to integrate the product into our workflow.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • March 2, 2016 at 9:09 am

    #1862442

    As Red Gate Ecosystem partners, you may want to try a new documentation SQL Server data flow visualization tool.

  • March 2, 2016 at 9:17 am

    #1862448

    agallo 79011 (3/2/2016)

    As Red Gate Ecosystem partners, you may want to try a new documentation SQL Server data flow visualization tool. Please see us a http://www.genesisonesolutions.com.[/quote%5D

    While this may be a fantastic tool, tagging it onto the end of a thread about code analysis amounts to spamming.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • March 2, 2016 at 10:43 am

    #1862495

    Please forgive the comment...and hopefully it can be removed asap 🙂

  • October 3, 2016 at 8:42 am

    #1904425

    I know this post is quite old, but I was just doing some research into this very subject and found quite a few tools. I have not used any of them, so I can not speak to their performance. I am just posting these here to help out the next guy as accumulating this list was rather tedious.

    - http://www.ubitsoft.com/products/sqlenlight/

    - https://www.devart.com/dbforge/sql/studio/sql-analyzer.html

    - https://www.dbbest.com/products/t-sql-analyzer/

    - http://sqlcop.lessthandot.com/- no custom tests.

    - http://sqlcodeguard.com/- no custom tests.

    - http://www.manduka.tech/#/home - very limited tests for t-sql. not sure if custom tests can be made

    - https://github.com/chrisoldwood/SS-Cop- pure t-sql. interesting from a theoretical point of view. extremely limited

  • October 15, 2016 at 8:19 am

    #1906748

    Hi, I've been searching and working on this topic for a while, and there are not tools to cover all scenarios. While there are "standard" rules that should be applied, companies have their own rules. We just found a "feasible" way to create and extend TSql static code analysis rules with a great results. I will be happy to share my experiences

    Regards

  • October 15, 2016 at 2:24 pm

    #1906760

    acortina68 (10/15/2016)

    Hi, I've been searching and working on this topic for a while, and there are not tools to cover all scenarios. While there are "standard" rules that should be applied, companies have their own rules. We just found a "feasible" way to create and extend TSql static code analysis rules with a great results. I will be happy to share my experiences

    Regards

    I've used SQLEnlight before and it allows the implementation of custom rules in a way which I found to be 'feasible'. Perhaps you would give an example of some of the things you found it incapable of doing?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • October 22, 2016 at 2:48 pm

    #1908177

    Hi, while SQLEnlight gives you good options, it does not integrate in a natural way with SSDT, which is the tool we use in our DLM. We have customized or extended the static code analysis for other languages c#, so we just wanted to do it in the same way for TSQL, so using DacFx and ScriptDom API we found a way to do it.

    We created 3 files:

    dll, implements the rules

    xml, customizes the rules

    xsd, describes the elements in the xml file

    To install the rules you just need to copy the files to the Visual Studio extension folder. VS will recognize the rules automatically.

    We are using this approach in our CI process too with great results.

  • October 22, 2016 at 4:30 pm

    #1908184

    I think that people spend way too much on cop-like programs like this. SELECT * isn't necessarily a problem (in fact, I've seen it provide a performance improvement on occasion. "It Depends".). Nested Views and Functions aren't necessarily a problem. Cursors, While Loops, and Recursion isn't necessarily a problem. Although I hate to see it, using other than 2 part naming conventions isn't necessarily a problem.

    There are a ton of things that can appear syntactically that a lot of people might think are a problem and will show up in a lot of these cop-like programs but the one thing they don't do is determine if the things it finds are actually a problem.

    Right clicking on an instance in explorer and drill down to one or more of the performance reports is much more effective at determining if you have a performance problem and the top 10 items are. If you don't like the output from that (can't copy and paste, for example), write your own. It's just not that difficult and you'll learn something in the process.

    Don't chase ghosts or chance problems. Find the real problems and fix those.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • January 23, 2017 at 3:45 pm

    #1924498

    acortina68 - Saturday, October 22, 2016 2:48 PM

    Hi, while SQLEnlight gives you good options, it does not integrate in a natural way with SSDT, which is the tool we use in our DLM. We have customized or extended the static code analysis for other languages c#, so we just wanted to do it in the same way for TSQL, so using DacFx and ScriptDom API we found a way to do it. We created 3 files:dll, implements the rules xml, customizes the rulesxsd, describes the elements in the xml fileTo install the rules you just need to copy the files to the Visual Studio extension folder. VS will recognize the rules automatically.We are using this approach in our CI process too with great results.

    Hi acortina68,
     Do you mind sharing your visual studio solution, I'm currently researching on this topic as well and I'm leaning towards the DacFx approach on extending the static code analytics for SSDT. Thanks in advance, my email address is eatierra@gmail.com if you can email me please. Thanks so much.

  • April 3, 2017 at 3:11 pm

    #1936425

    eatierra-514507 - Monday, January 23, 2017 3:45 PM

    acortina68 - Saturday, October 22, 2016 2:48 PM

    Hi, while SQLEnlight gives you good options, it does not integrate in a natural way with SSDT, which is the tool we use in our DLM. We have customized or extended the static code analysis for other languages c#, so we just wanted to do it in the same way for TSQL, so using DacFx and ScriptDom API we found a way to do it. We created 3 files:dll, implements the rules xml, customizes the rulesxsd, describes the elements in the xml fileTo install the rules you just need to copy the files to the Visual Studio extension folder. VS will recognize the rules automatically.We are using this approach in our CI process too with great results.

    Hi acortina68,
     Do you mind sharing your visual studio solution, I'm currently researching on this topic as well and I'm leaning towards the DacFx approach on extending the static code analytics for SSDT. Thanks in advance, my email address is eatierra@gmail.com if you can email me please. Thanks so much.

    Hi acortina68,
    I am also interested in automation of my teams code analysis activities through VS, can you share what you have found here? many thanks in advance (or I can get you to email me)

  • April 4, 2017 at 4:00 am

    #1936481

    Jeff Moden - Saturday, October 22, 2016 4:30 PM

    I think that people spend way too much on cop-like programs like this. SELECT * isn't necessarily a problem (in fact, I've seen it provide a performance improvement on occasion. "It Depends".). Nested Views and Functions aren't necessarily a problem. Cursors, While Loops, and Recursion isn't necessarily a problem. Although I hate to see it, using other than 2 part naming conventions isn't necessarily a problem.There are a ton of things that can appear syntactically that a lot of people might think are a problem and will show up in a lot of these cop-like programs but the one thing they don't do is determine if the things it finds are actually a problem.Right clicking on an instance in explorer and drill down to one or more of the performance reports is much more effective at determining if you have a performance problem and the top 10 items are. If you don't like the output from that (can't copy and paste, for example), write your own. It's just not that difficult and you'll learn something in the process.Don't chase ghosts or chance problems. Find the real problems and fix those.

    That last sentence is important, but it also means you should be looking at SELECT * and other potential code smells or potential issues. These tools do help you to determine where to look. The key is that if SELECT * isn't a problem, then add an exception to remove the flagging from the tool.
    There isn't an issue with the tools, it's with blind use.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply