Detecting T-SQL code smells – Part 1

When moving a new SQL server database into production or taking over maintenance duties, you might want to perform some audit’s first. Audit reports make it easy to identify issues and rais additional questions.

Detecting T-SQL code smells!

This series of blog posts will cover the following;

• Automating the process of extracting T-SQL code from a given database. (Part 1)
• Explore how to use the “TSQL Code Smells Finder” as found on codeplex.com (Part 2)
• Creating new rules and implement other customization’s (Part 3)

Note: This post mainly focuses on production DBA’s scenarios however, the solution can be altered to suit your needs.

Part 1 – Automating the process of extracting T-SQL code from a given database.

First of all, we need to gather the T-SQL code for further analyses. The analysis will be performed outside of the SQL server execution context, avoiding possible performance issues. Fortunately, fetching the source code is very simple when using the Scripter class (located within the Microsoft.SqlServer.Management.Smo namespace).

Note: This process can be eliminated if you have the database deployment scripts at hand..

The steps are as following:

• Establish a connection with the SQL instance
• Create and configure the Scripter object
• Iterate through all relevant database objects
• Use the scripter object to write the data to disk.

The script

To view the results add the following line at the end

This process of Detecting T-SQL code smells is relatively simple, as you can see. The next post in the series will cover parsing the results.