Check your T-SQL code!

Back in 2005, I changed the technology stack from Java to .NET and at first in Visual Studio I really lacked the capabilities that IntelliJ IDEA had. Trying different plugins for the studio, I stopped at ReSharper and use it to this day. Recently it became interesting whether there are similar products for working with SQL Server, and more specifically with the T-SQL code of stored procedures. Actually, I want to tell you about one such product in this article.

So, SqlCodeGuard is a free addin for SQL Server Management Studio for static analysis of T-SQL code. Well, plus a couple of other features.

Main features

')

Search for potential errors in the code

As far as I understand, more than a hundred situations of various orientations are supported, from “it’s better not to do so” to “and here it’s guaranteed to fall down” and “the cause of the brakes is it.”

Finding object dependencies

Allows you to determine who and where is used (which tables the procedure / function / view uses) and solves the inverse problem - allows you to determine who uses this or that object, and for tables and views - how (!) Is used - data selection / insert / update / delete.

List of objects that were not found in the database being analyzed

A side opportunity. Allows, for example, to track typos in the names of tables, procedures, etc. As you know, SQL Server allows you to create a procedure that uses a nonexistent procedure or table or delete an object that is used in procedures / views. Such errors will appear only at the execution stage, so finding them at the coding stage is a considerable plus.

Tree view of the code structure

Useful for complex procedures - allows you to quickly view / understand the logic of a complex code as well as assess the complexity of the procedure / function / presentation in certain units of complexity “P”. At the same time counts the number of items.

Product Benefits ...

• Free :)
• Big set of rules
• The ability to check as a separate request and the entire database as a whole
• Integration with Visual Studio and Database Project
• Speed ​​(DB of 3000 objects is checked in 17 seconds, of 30K objects - 150 seconds)
• A decent set of rules for locating operators and language constructs that are declared obsolete and whose support will be removed (or even already removed) in the near future. Allows you to quickly analyze the database when migrating to new versions of the server.

And, which is of particular interest for group development in larger companies - a special API is provided that allows you to use the code verification capabilities in your applications. For example, you can embed SQL verification in your build-process or implement the Checkin Policy.

... and its flaws

• Insufficient documentation. However, the interface itself is quite simple, but it would be nice to describe the verification rules in more detail.
• It is not clear how to integrate SqlCodeGuard and Database Project. Support on the site is stated, but it is not clear - and how, actually?
• Some exotic language constructs are not recognized. Also, sometimes it stumbles on keywords as identifiers (names of tables, columns, etc.).

It would be nice if there was an opportunity out of the box to use integration with continuous integration, but so far this is not the case.

Brief summary

In general, despite certain shortcomings, SqlCodeGuard is quite suitable for use both by individual developers and, probably, by development teams for providing a uniform code writing policy as well as for simplifying the analysis of existing databases.