SQL Server - Schema/Code Analysis Rules - What would your rules include?

Question

We're using Visual Studio Database Edition (DBPro) to manage our schema. This is a great tool that, among the many things it can do, can analyse our schema and T-SQL code based on rules (much like what FxCop does with C# code), and flag certain things as warnings and errors.

Some example rules might be that every table must have a primary key, no underscore's in column names, every stored procedure must have comments etc.

The number of rules built into DBPro is fairly small, and a bit odd. Fortunately DBPro has an API that allows the developer to create their own. I'm curious as to the types of rules you and your DB team would create (both schema rules and T-SQL rules). Looking at some of your rules might help us decide what we should consider.

Thanks - Randy

Answer 1

Some of mine. Not all could be tested programmatically:

• No hungarian-style prefixes (like "tbl" for table, "vw" for view)
• If there is any chance this would ever be ported to Oracle, no identifiers longer than 30 characters.
• All table and column names expressed in lower-case letters only
• Underscores between words in column and table names--we differ on this one obviously
• Table names are singular ("customer" not "customers")
• Words that make up table, column, and view names are not abbreviated, concatenated, or acronym-based unless necessary.
• Indexes will be prefixed with “IX_”.
• Primary Keys are prefixed with “PK_”.
• Foreign Keys are prefixed with “FK_”.
• Unique Constraints are prefixed with “UC_”.