SQL SERVER – Pre-Code Review Tips – Tips For Enforcing Coding Standards

Each organization has its own coding standards and enforcement rules. It is sometime difficult for DBAs to change the code following code review, as it may affect many different layers of the application. In large organizations, many stored procedures are written and modified every day. It is smart to keep watch on all stored procedures, at frequent intervals, before code comes to final code review. Pre-code reviewing in this manner will save lots of time. I run a few scripts every day to check the status of all the stored procedures on our development server. Doing so gives me a good indication about which stored procedures are not up to coding standards. A daily pre-code review like this keeps surprises out of the final review.

Read my complete article SQL Server Pre-Code Review Tips

Read SQL SERVER Database Coding Standards and Guidelines Complete List Download

What I Look For in a Daily Pre-Code Review

The scripts do not need to be fancy. Most of my checks are simple searches in sys.sql_modules, joined to sys.procedures and filtered on modify_date, so I only look at procedures that changed since yesterday. That keeps the list short, and short lists actually get read. I also save the results in a small table, so I can see which issues come back week after week.

Here are the kinds of things I search for:

• Procedure names that start with sp_. SQL Server treats that prefix as a system procedure and looks in master first, which adds work and can even run the wrong procedure.
• SELECT * in procedures that feed an application.
• A missing SET NOCOUNT ON at the top of the procedure.
• Tables referenced without the schema name.
• Cursors, and hard-coded values that belong in a table.

The point is timing. A problem found the same day is a five minute fix for the developer who just wrote it. The same problem found in the final review may touch the application, the reports and the tests. I share the findings in a friendly way, as a short list, not as a scolding. People fix things faster when they do not feel blamed.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.