Detecting T-SQL code smells – Part 1

Detecting T-SQL code smells – Part 1

When moving a new SQL server database into production or taking over maintenance duties for large and heavenly used databases, you might want to perform some audit’s allowing you to ask some additional questions when the development team is still available or just being aware of possible performance issues / special maintenance routines etc.

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 (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, gathering the 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 just add the following line at the end

This is all fairly simple as you can see. The next post in the series will cover parsing the results.

Post Navigation