A StyleCop/FxCop For Databases

My team all use the most excellent ReSharper during day-to-day C# coding as it performs some great on-the-fly static code analysis. We also occasionally run FxCop to provide some further insights into our C# code. However we didn’t really have anything for the database side of things, so a colleague of mine wrote one and called it ‘DbCop’. OK, so it’s clearly not in the same league as ReSharper but it has got me wondering if there are any commercial products out there that fill this space? I’ve not specifically hunted for one, but you still see things like this mentioned (if they’re worth anything) on sites like StackOverflow and personal blogs. I also dropped some not-so-subtle hints at the Red Gate stand at this years ACCU Conference but they didn’t give anything away if they do have anything like this in the pipeline...

Our tool is really nothing fancy, just some simple SQL scripts that spelunk the system tables and look at the schema metadata for some common mistakes and coding convention violations. It runs at the end of our database Continuous Integration build and generates a report; but it doesn’t fail the build if it finds a problem because we currently have no exclusion mechanism in place (besides manually hard-coding one into the script). We also have a separate schema/namespace called “build” for the DbCop objects so that they are not applied to integration test/system test/production databases by accident.

So far it only checks the following:-

• No reserved words have been used for the names of our objects
• The names of tables, columns and parameters adhere to our coding conventions
• Table columns are defined using our UDT’s, not primitive types
• Each table has a primary key
• Each table has a clustered index

Clearly rules like the last two are made to be broken, but as a general rule they are pretty sound. Personally I tend to avoid a Primary Key rule violation by writing a unit test in the first place that ensures duplicates are disallowed when appropriate. It would be great to include spell checking into the process (just like FxCop) because I’m forever misspelling identifiers and with a database it’s much harder to change table and column names after they’ve gone into production and contain millions of rows.