Code Review Tools for Transact SQL

Question

I am looking for automated code review tools for transact sql

Answer 1

SQL refactor

SQL Refactor allows you to automatically format your SQL code and database objects in the style of your choice. SQL Refactor improves the quality of your code, making it easier to read, understand and share amongst a team. A companion tool to SQL Prompt, SQL Refactor dramatically speeds up database development and administration, without impacting on the behavior of your database and applications.

Answer 2

There are a couple of tools baked into SQL Server that you should take advantage of.

The first is the Query Execution Plan. In a query window, hit Ctrl + L and take a look at how the query is being executed. This helps immensely with identifying bottlenecks and potentially expensive table scans.

The second is to use the Database Engine Tuning Advisor. This won't refactor your query, but it will help you identify indices you should place on your tables to make your queries run much more efficiently.

A good third party tool (not free) is Red Gate's SQL Refactor. Red Gate makes a lot of solid products for SQL Server administration, so I encourage you to check them out.

Answer 3

I am not aware of any existing tool which would do that but you might have some luck with training PHP CodeSniffer to tokenize T-SQL and by creating your own sniffs. It's not an easy task so let me know if you will find a better way.

UPD: there are SQL tokenizers already created for PHP: Justin Carlson's one, also a txtSQL.tokenizer.txt. This means that if you adapt them to be used by the CodeSniffer you're ready to go.

Answer 4

As far as I know there are no tool that will find every problem you may have with all different kinds of datamodels and SQL Statements. But there are some tools that may find the most basic errors, like Visual Studio 2008 Database edition. The code will be evaluated against a set of rules everytime you "compile" your project. You can define your own rules I think, but haven't tried yet.

Another thing that may help you to get the code more correct from the beginning is templates. Define your templates as you want them and make sure everyone is using them.