Red Gate's SQL Refactor Boosts DBA Productivity

Summarize Script

Summarize Script works a little differently than the first two features. It is designed to take long blocks of code and group it to make the code easier to understand. For example, it is not unusual for me to be asked by a report developer to check out a stored procedure to see how I can boost its performance. Sometimes, these stored procedures run hundreds of lines of code, making it hard to understand what the code is doing.

The Summarize Script feature takes a long script and summarizes it so that is it is more understandable from a “big picture” perspective.

Let’s take a look at the long stored procedure we used earlier.

As I mentioned before, this is just a brief look at a very long script. If I want to get an overview of what the code is doing, I can ask SQL Refactor to produce a Summary Script, which looks like the following.

What I see above is a high-level view of what this stored procedure is doing. Instead of having to wade through over 800 lines of code to find out what the stored procedure is doing, I can see what is happening very quickly. While I can’t know all of the functionality of this stored procedure from this overview, at least I can get a quick overview of what is happening, which will help me focus where in the stored procedure I need to spend my time.

In addition, if I want to quickly locate in the code where a particular statement is occurring, I can highlight the statement in the window above, and then this exact code is displayed in the window, making it easy for me to find it, as shown below.

While you won’t need this feature for short code, it can come in very handy for figuring out how long code works.

Find Unused Variables and Parameters

Over time, as Transact-SQL code changes, it is sometimes possible that variables or parameters that were once used in the original code are no longer used, and have been orphaned. In other words, the code now has declared variables or parameters that aren’t being used. This doesn’t prevent the code from working correctly, but having orphaned declared variables and parameters uses unnecessary resources and should be removed to reduce resource usage and to make the code more readable.

SQL Refactor has the ability to search a Transact-SQL script and identify any unused variables and parameters so they can be manually removed. For example, check the following code from a stored procedure. At the top of the stored procedure you see two parameters being declared, but they aren’t used in the stored procedure. Also note that @Company and @EndingDate are underlined. They are underlined because I told SQL Refactor to look for unused variables and parameters, and it found these two and underlined them. At this point, if you should desire, you can remove them. They are not removed for you.

One of the things I noticed about this feature, which was different from the features we have already explored, is that it appears to look at all of the code in the window, and that you can’t select a portion of the script, and only check the selected section. I guess this makes sense as why would you look for unused variables or parameters in only a subset of a script.

SQL Refactor also detects variables that are assigned values, but the assigned value is never used. This is a more complex way of determining whether a variable is just wasting space and makes code unreadable, or whether it is actually a contributing part of the Transact-SQL code.

While this feature probably won’t be the reason you purchase SQL Refactor, it can come in handy at times.


Leave a comment

Your email address will not be published.