Extensible rule-based tool to find problems in stored procedures?

Question

Post reply

Extensible rule-based tool to find problems in stored procedures?

michael_c_leone

Grasshopper

Points: 20
More actions
August 31, 2012 at 11:57 am

#263631

Hello,
Is there a extensible rule-based tool to find problems in SQL Server stored procedures? What I'm looking for is something similar to:
http://www.mono-project.com/Gendarme
...but only for stored procedures instead of .NET app code (in our case VB). We've found Gendarme to be very useful so far and it got me thinking it would be great to have the same capability to analyze stored procedures for bad practices, etc (including any rule we want it to look for per the extensibility).
Thanks, Michael

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

not sure about extensibility, but i've seen two tools that look for issues, complexity, and rules for coding;

http://sqlcop.lessthandot.com/

http://www.sqlcodeguard.com/

I'm not familiar with either one, but it's a starting point;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

michael_c_leone Grasshopper Points: 20 More actions · Answer 2

Thanks! It looks like SqlCodeGuard is just want I was looking for. Not sure how extensible it is, or if it will be easy to get the results into a database where we can report and track, but it is a great start.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 3

I demoed Visual Studio features at one point and the rules are extensible.

Create and Register Additional Rules for Analyzing Database Code - Visual Studio 2010

Analyzing Database Code to Improve Code Quality - Visual Studio 2010

Not a lot of shops adopted the database tools integrated in versions of Visual Studio 2010 and before because it was cost prohibitive, instead opting for some combination of SSMS and other tools. The good news is that Microsoft has carried static code analysis forward into SSDT for SQL 2012 which is free 😀 I havent demoed it yet though to see how much they brought forward.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlcodeguard Grasshopper Points: 15 More actions · Answer 4

michael_c_leone (8/31/2012)
Thanks! It looks like SqlCodeGuard is just want I was looking for. Not sure how extensible it is, or if it will be easy to get the results into a database where we can report and track, but it is a great start.

from version 2.1.4628 SqlCodeGuard has an API to incorporate it in your own projects

usage is pretty simple

SqlCodeGuardAPI.API api = new SqlCodeGuardAPI.API();

api.IncludeIssue("ALL");

bool unparsed;

List<Issue> l = api.GetIssues(sql, out unparsed);

if (unparsed)

{

Console.WriteLine("Unparsed! Issue list may be incomplete or wrong");

}

Console.WriteLine("Issues:");

foreach (Issue i in l)

{

Console.WriteLine(String.Format("({0}){1} at {2}:{3} ({4})", i.ErrorCode, i.ErrorText, i.Column, i.Line, i.ErrorMessage));

}