Any Static Code Analysis Tools for Stored Procedures?

Question

Are there any static code analysis tools for stored procedures written particularly in PL/SQL and T-SQL?

There is a free tool call Manduka (http://manduka.tech) which supports both PL/SQL and T-SQL — Lasitha Weerasinghe, Sep 28 '16 at 07:07

score 7 · Accepted Answer · answered Feb 10 '09 at 15:14

7

For T-SQL, Microsoft has the database edition of VS Team Suite (although, I believe its now in the dev SKU). This link talks about writing your own static code analysis rule for T-SQL: http://blogs.msdn.com/gertd/archive/2009/01/01/creating-t-sql-static-code-analysis-rules.aspx

answered Feb 10 '09 at 15:14

MichaelGG

9,976
1
39
82

1

Related reading http://msdn.microsoft.com/en-us/library/dd172133.aspx, http://msdn.microsoft.com/en-us/library/dd193244.aspx – suyasha Feb 11 '09 at 08:15
I would accept this answer as well. Its just that becuase my question has two parts (one for PLSQL and other for TSQL) there are two separate answers but stackoverflow allows only one to be accepted. – suyasha Feb 17 '09 at 07:50

score 6 · Answer 2 · answered Feb 12 '09 at 11:27

6

Oracle has some little-known stuff built in.

Try this in 10g Release 2 or above:

ALTER SESSION PLSQL_WARNINGS = 'ENABLE:ALL';

Then compile your PL/SQL package (not an anonymous block).

answered Feb 12 '09 at 11:27

WW.

23,793
13
94
121

score 4 · Answer 3 · answered Jul 02 '13 at 10:12

4

Try free Sql Code Guard. It provides fast and comprehensive static analysis for T-Sql code, shows code complexity and objects dependencies

answered Jul 02 '13 at 10:12

Alexander Sigachov

1,541
11
16

score 4 · Answer 4 · answered Feb 12 '09 at 20:07

4

Toad features the CodeXpert utility to statically check your PL/SQL code.

The utility can either scan files or connect directly to the Oracle database. As far as I've seen, works only on Windows.

answered Feb 12 '09 at 20:07

Vladimir

6,853
2
26
25

score 3 · Answer 5 · answered Jul 27 '09 at 08:53

3

For PL/SQL, Toad CodeXpert can be extended with Sonar, an open source tool to manage code quality through a plugin.

I guess it would be possible to write a plugin for T-SQL as well.

answered Jul 27 '09 at 08:53

Ira Baxter · Answer 6 · 2010-10-06T15:32:04.177

Our SD Source Code Search Engine is a tool for interactively searching large source code bases in many computer languages (including PL/SQL) efficiently, by preindexing the source code files by their tokens (identifiers, numbers, etc). As a side effect of the preindexing step, it computes standard metrics: SLOC, Cyclomatic, Halstead, ... for each file and produces a report. See the site for an example.

Our SD CloneDR is a tool for analyzing large code bases for redundant code. We've applied it to PL/SQL, and have seen something like 45% of the code involved in clones. YMMV. The CloneDR works with a large variet of langauges; see the site for sample clone detection runs on some of those other langauges.

EDIT 10/4/2010:

Our Source Code Search Engine is designed to enable very fast searches across large codes of mixed languages. It succeeds by preindexing the source code. As a side effect of the indexing step, it compute a variety of metrics including Cyclomatic and Halstead measures of complexity.

Just added: not a static analysis tool, but one generally of interest to people interested software quality: our TestCoverage for PLSQL stored procedures.

score 2 · Answer 7 · answered Feb 10 '09 at 08:18

2

The nearest thing that I know of are the estimated and actual query plan functions available in SQL Server Management Studio but I'd guess there's similar for other SQL engines.

answered Feb 10 '09 at 08:18

Garry Shutler

32,260
12
84
119

score 1 · Answer 8 · answered Feb 10 '09 at 09:34

1

Not that I'm aware of. Regardng PL/SQL, since Oracle doesn't expose much of their PL/SQL compilation engine its hard to find tool support.

The most I've been able to do is to query the data dictionary to do things like map the package dependencies.

answered Feb 10 '09 at 09:34

darreljnz

2,300
2
18
18

2

So one has to have another engine that can handle PL/SQL. See http://www.semanticdesigns.com/Products/LanguageTools/PLSQLTools.html – Ira Baxter Oct 06 '10 at 15:36

score 0 · Answer 9 · edited Mar 12 '16 at 19:10

As a followup on the post for T-SQL; VS2010 and VS2008 database development editions come with build-in static analysis rules. These are user extensible, i.e. you can write your own analysis rules in a .net language. The open-source project Neznayka comes with an initial set of 22 rules, and forms a useful base for you to start contributing your own.

Admittedly you have to fork out for Visual Studio and be prepared to develop db code using VS projects, but the recent releases of SSDT as a plug-in for SSMS raises the prospect that Microsoft may, if it has the will-power to do so, allow user-written rules to be distributed to environments that use MS SQL projects 'for free' to develop database code.

The link for Neznayka.

Or search the web.

Any Static Code Analysis Tools for Stored Procedures?

9 Answers9

Linked