Why I Developed SQL Code Guard: A Fairy Tale from a DBA

A long time ago, in a galaxy far, far away….

About ten years ago, I realized I was spending more than 75% of my business day writing T-SQL code. It was cool – yet awful at the same time! Cool ‘cause I became a ReAl TsQl GuY :) and awful as I had no good tools to do my job better. When I was working with C++, Delphi – even Borland Pascal – I had a wide range of code-proofing tools. But T-SQL doesn’t have ANY! Yes, we had MSBPA (SQL Server 2000 Best Practices Analyzer) - but it was impossible to use it in everyday development or continuous integration processes.

Therefore, as a TRUE programmer, I made the only sensible decision – to create my own tool! With bells and whistles, of course. And furthermore, I’ve made the tool available to the SQL community for free!
It took quite a lot of time to make the dream come true, but one fine day it was finally done, and now SQL Code Guard (SCG) is born!

So, what does it do?

“First of all, the answer to the most central question, where are errors most likely to occur?
Potential Error Search
SCG is able to find and show hundreds of different situations starting with “not advisable”, to “this is going to fail here for sure” and “here’s what makes it so slow’.
I used AdventureWorks2012 as a sample database – and, rather surprisingly, found a careless error in one of the procedures! (Yep, nobody’s perfect, even Microsoft ;)

Another age-old question is: What is using this table,  procedure, function etc., and how?

Object Dependency Search
This feature works out which objects are used by which procedures, functions and views and also solves the inverse problem, determining what is used by which object.

Note that for tables and views it shows how the objects are used, for example by insert/update/delete etc.

List of Objects Not Found in Analyzed DB
It is commonly known that SQL Server allows the creation of procedures that use non-existent procedures or tables or the deletion of objects used in procedures. Such errors reveal themselves only at execution time so being able to identify them in the coding process is a great advantage.

Code Outline
Useful for complicated procedures – allows complicated code to be quickly understood and reviewed ,and the complexity of procedures and functions to be evaluabted in some ‘P’ complexity units.

BTW, SCG calculates code complexity and other measures of code (for example – the quantity of statements in an object) which might be useful to identify some “overdesigned” objects.

Conclusion
In addition, as a result of testing SCG in a number of organizations, an API to expose SCG’s functionality was developed, allowing integration to one’s own applications and build processes. It goes without saying that we must not forget MSBuild support. The integration is basic only for now but this will be improved over time

To summarize, SCG has been developed to be useful for wide variety of users, from individual developers to software development shops whose managers have perhaps adopted a C# code policy, and want to implement the same for T-SQL, and have no idea how.

Don't be shy - visit the SQL Code Guard website at http://sqlcodeguard.com and make you scripts better – after all, it's free!
If you have any questions, suggestions, feature requests or bug reports – please post them to the SCG forum at http://sqlcodeguard.com/forum/