Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-SQL Code Analysis Tools


T-SQL Code Analysis Tools

Author
Message
Andrew Notarian
Andrew Notarian
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 429
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?
Andrew Notarian
Andrew Notarian
SSChasing Mays
SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)SSChasing Mays (625 reputation)

Group: General Forum Members
Points: 625 Visits: 429
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.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8414 Visits: 19517
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
agallo 79011
agallo 79011
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
As Red Gate Ecosystem partners, you may want to try a new documentation SQL Server data flow visualization tool.
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8414 Visits: 19517
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 www.genesisonesolutions.com.


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


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
agallo 79011
agallo 79011
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 40
Please forgive the comment...and hopefully it can be removed asap :-)
spcghst440
spcghst440
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 138
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
acortina68
acortina68
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 77
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
Phil Parkin
Phil Parkin
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8414 Visits: 19517
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?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
acortina68
acortina68
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 77
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search