I have been using FxCop and StyleCop tools to get the static code analysis report for C# code to improve the code quality. Sometime back, I started looking for similar tools which can do the static code analysis for the database code written in SQL Server. Surprisingly, I couldn't figure out any tools available in the market. All I have found are the below 14 rules suggested by Microsoft which can be configured within the Visual Studio.
http://msdn.microsoft.com/en-us/library/dd193411(v=vs.90) – Design rules
http://msdn.microsoft.com/en-us/library/dd172117(v=vs.90) – Performance rules
http://msdn.microsoft.com/en-us/library/dd193246(v=vs.90) – Naming rules
After going through the above URL's, I realized that the 14 rules are very limited and there is a lot of scope to add more rules to the list. I have gone through the MSDN forums and it seems Microsoft is not going to incorporate any static code analysis tools in the next version / near future.
With that, I started exploring how I can build my own tool which can take DB scripts as input, evaluate the script against the set of rules and generate a report with any violations.
After doing lot of googling, I identified how the SQL files are parsed in SSMS. SSMS uses a set of .net libraries for parsing the scripts written in SQL Server. Please read http://daravind.blogspot.in/2013/04/parsing-sql-server-script-using-c.html to know more details about that. I wanted to leverage the same set of libraries and started building a POC to incorporate the 14 existing rules defined by Microsoft.
With the POC, I spent a good amount of time with my peers and started looking at how we can build this as a product which can be taken to the market. Lots of great ideas came out of the discussion from my peers and we started working on building this product. We came up the list of 60+ rules which can be validated against each and every script uploaded to the tool. The user will have the flexibility to select the rules against which the script can be validated.
We built the first version of the tool as a windows application and later we wanted to expose the same as a Web application / WCF service and would like to build a plug-in for Visual Studio and SSMS. We have successfully completed the initial version of the web application. The trial version of the application can be evaluated from http://sqlanalyser.happiestminds.com with a simple registration.
In a nutshell, SQL Analyzer is code analysis tool for Microsoft SQL Server which evaluates SQL scripts written in SQL Server against the best practices provided by Microsoft and Happiest Minds practices and provides recommendations for improvement. SQL Analyzer works as both static and dynamic code analysis tool. SQL Analyzer identifies the defects in early development cycle. It helps you to release the quality of database code to production and saves the cost of fixing the defect late in production cycle.
With new ideas coming in, the product is evolving and now we are focusing on how to incorporate dynamic code analysis by connecting toe the database and fetch the scripts.
Please read the below blogs written by my peers on the tool:
http://dattatreyakulkarni.blogspot.in/2012/09/tool-based-ms-sql-script-review-for.html
http://dattatreyakulkarni.blogspot.in/2012/09/sql-analyzer-part-ii.html
http://architasblog.blogspot.in/2013/01/sql-analyzer.html
Will write more in coming blogs about the usage and the different rules incorporated in the tool ....
http://msdn.microsoft.com/en-us/library/dd193411(v=vs.90) – Design rules
http://msdn.microsoft.com/en-us/library/dd172117(v=vs.90) – Performance rules
http://msdn.microsoft.com/en-us/library/dd193246(v=vs.90) – Naming rules
After going through the above URL's, I realized that the 14 rules are very limited and there is a lot of scope to add more rules to the list. I have gone through the MSDN forums and it seems Microsoft is not going to incorporate any static code analysis tools in the next version / near future.
With that, I started exploring how I can build my own tool which can take DB scripts as input, evaluate the script against the set of rules and generate a report with any violations.
After doing lot of googling, I identified how the SQL files are parsed in SSMS. SSMS uses a set of .net libraries for parsing the scripts written in SQL Server. Please read http://daravind.blogspot.in/2013/04/parsing-sql-server-script-using-c.html to know more details about that. I wanted to leverage the same set of libraries and started building a POC to incorporate the 14 existing rules defined by Microsoft.
With the POC, I spent a good amount of time with my peers and started looking at how we can build this as a product which can be taken to the market. Lots of great ideas came out of the discussion from my peers and we started working on building this product. We came up the list of 60+ rules which can be validated against each and every script uploaded to the tool. The user will have the flexibility to select the rules against which the script can be validated.
We built the first version of the tool as a windows application and later we wanted to expose the same as a Web application / WCF service and would like to build a plug-in for Visual Studio and SSMS. We have successfully completed the initial version of the web application. The trial version of the application can be evaluated from http://sqlanalyser.happiestminds.com with a simple registration.
In a nutshell, SQL Analyzer is code analysis tool for Microsoft SQL Server which evaluates SQL scripts written in SQL Server against the best practices provided by Microsoft and Happiest Minds practices and provides recommendations for improvement. SQL Analyzer works as both static and dynamic code analysis tool. SQL Analyzer identifies the defects in early development cycle. It helps you to release the quality of database code to production and saves the cost of fixing the defect late in production cycle.
With new ideas coming in, the product is evolving and now we are focusing on how to incorporate dynamic code analysis by connecting toe the database and fetch the scripts.
Please read the below blogs written by my peers on the tool:
http://dattatreyakulkarni.blogspot.in/2012/09/tool-based-ms-sql-script-review-for.html
http://dattatreyakulkarni.blogspot.in/2012/09/sql-analyzer-part-ii.html
http://architasblog.blogspot.in/2013/01/sql-analyzer.html
Will write more in coming blogs about the usage and the different rules incorporated in the tool ....
No comments:
Post a Comment