SQL Server Corporate Standards - Automated Compliance Checking

Question

SQL Server Corporate Standards - Automated Compliance Checking

Tim Walker.

SSCertifiable

Points: 5173
More actions
May 21, 2013 at 3:43 pm

#273010

Does anyone know of a flexible automated SQL Syntax checker?
I want to be able to analyse scripts before they are deployed to our test systems to check for certain things such as the fact that each statement has an appropriate error check after it.
I want to make sure each statement is commented.
I want to spot syntax that would be legal in an on premise SQL Server but not in Azure.
I am expecting to configure my own rules.
Does such a thing exist? I have started to write one but clearly its complicated because I am effectively having to start to simulate SQLs own parser so I can break down and analyse statements.
Any feedback will be much appreciated (positive or negative).
Thanks
Tim
.

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

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

Mike Good SSCertifiable Points: 7415 More actions · Answer 1

I do not know of such a tool. But one of my coworkers years ago developed something similar years ago, based on Linchi Shea book regarding using Perl to help admin MS SQL. It worked, was not overly complex. At that time, Perl was one of easiest ways to get regular expressions; now probably C# or whatever language you want can do this (probably not TSQL, though?). You don't have to be too tricky/accurate with the parsing, I think, just get regular expressions that work 90% of the time, or wherever you set your bar.

However, my main reason for replying is if you're on SQL 2008, I think should not be checking error after every statement, should be using try/catch? I also think commenting every stmt is pretty excessive--but this is just me.

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQLCop does some code valdiation, along with other things it tests for; not sure if it is customizable o check for specific things like comments

http://sqlcop.lessthandot.com/detectedissues.php

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!

EdVassie SSC Guru Points: 60472 More actions · Answer 3

It is easy for people to look at what might make their own job easier, but if you get paid for your work you need to look at what gives the most benefit to the business.

If you want to check SQL standards to any extent, do you already have buy-in from your management and the development team. Are they happy that your proposals are the best means to achieve the level of standards checking that the business needs.

If you develop a process on your own without getting your customers (the developers) and suppilers (your management) to agree what you are doing, you will find it very difficult to enforce what you want to do.

Personally, I think that your description of what you want to do is overkill, and would bring very little benefit to the business. A process that focusses on TDD of SQL Server code would achieve what you probably want in terms of code reliability and comprehension, and also gives a real business advantage in reducing functional errors in delivered code.

Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 4

Mike, Lowell and Ed,

Thanks very much for your feedback. I accidentally unchecked the option to notify me of replies and assumed I hadn't got any!

Your different viewpoints and advice are much appreciated.

Thanks again.

Tim

.

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

I've been playing around with an 'addin' for VS2008 database projects to do simple coding standards checks eg non Alpha characters in object names, the use of tbl_, t_ prefixes, sp_ for User defined SP's, and on SP's things like WITH RECOMPILE, ENCRYPTION etc in the header.

It woks to a degree, but getting the other coding standards I need to enforce is more difficult, such as using AS before the Alias, general formating of code, tabbing in SP's is proving more difficult so I've parked it while I get it clear in my own head.

_________________________________________________________________________
SSC Guide to Posting and Best Practices