Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

A Review of SQL Refactor

By Dinesh Asanka,

Introduction

If you are a programmer who carries out day to day programming with SQL Server, you know how important it is to write code that some body else can read. In the real world, we rarely follow this. So, Red Gate, provider of essential SQL Server tools, has come up with a solution so that you can achieve this by simply clicking a menu item! Apart from that, there are a few other features that you will find addictive. For example, say you want to rename a table which has used in many views and stored procedures. This tool will provide an option for you to rename a table as well as all the places where it has used.

Installation and Configuration

The installation of SQL Refactor is a very simple operation, similar to other Red Gate tools. After installation a new menu item is added to SQL Server Management Studio. There is a known issue with Refactor, which may occur if you have previously installed SQL Server with the SP2 CTP. Red Gate has provided a fix for this issue which you can obtain from the Refactor development team.

Features Tested

All the features of SQL Refactor are available by means of a menu in SQL Server management studio of SQL Server 2005.

  • Lay Out SQL reformats your T-SQL scripts. There are over 30 options to control this feature. Lay Out SQL, and these options, can be accessed from the top-level SQL Refactor menu.
  • Smart Rename renames functions, views, stored procedures and tables, and updates all the references to these renamed objects. You can select this feature from the context menu in Management Studio's Object Explorer.
  • Smart Rename parameters and columns renames parameters of stored procedures and functions, and columns of tables and views. You can select this feature from the context menu in Management Studio's Object Explorer.
  • Split Table splits a table into two tables, and automatically rewrites the referencing stored procedures, views, and so on. You can also use this refactoring to introduce referential integrity tables. You can select this feature from the context menu in Management Studio's Object Explorer.
  • Uppercase Keywords turns keywords in your script or selection to uppercase.
  • Summarize Script provides you with an overview of your script. By highlighting items in this overview, you can see the corresponding statements highlighted in your script.
  • Encapsulate As New Stored Procedure turns your selection into a new stored procedure and, if requested, introduces a reference to it in your script.
  • Expand Wildcards expands SELECT * statements to include a full list of columns in the select part.
  • Find Unused Variables and Parameters shows you the variables and parameters in your script that are not used, or that are only assigned to.
  • Qualify Object Names modifies the script so that all object names are qualified.

To examine the features of this tool, let us work through an example.Assume that you want to list employees with their addresses. We are using the AdventureWorks Database, which comes with SQL Server 2005 for this example. The initial query will be:

select * from HumanResources.Employee Emp inner join HumanResources.EmployeeAddress EA on Emp.EmployeeId = EA.EmployeeId inner join Person.Address A on A.AddressId = EA.AddressId

Let us format this query. Simply click the Lay Out SQL option from the SQL Refactor menu. To put key words in capital, click the Uppercase Keywords option as well. This is what you get after clicking just two options.

SELECT *

FROM HumanResources.Employee Emp

INNER JOIN HumanResources.EmployeeAddress EA ON Emp.EmployeeId = EA.EmployeeId

INNER JOIN Person.Address A ON A.AddressId = EA.AddressId

Perhaps we dont want all the fields to be listed. Instead we want some basic fields. An obvious way of doing this is to drag and drop the column name from explore. But you have to enter the correct table alias along with the field name, which is time consuming. However, there is an easy way of doing this with SQL Refactor. You can click the Expand Wild Cards feature and remove unnecessary fields. This is what you see in SQL Sever Management Studio.

SELECT Emp.EmployeeID,

Emp.NationalIDNumber,

Emp.Title,

Emp.BirthDate,

Emp.MaritalStatus,

Emp.Gender,

Emp.HireDate,

A.AddressID,

A.AddressLine1,

A.AddressLine2,

A.City,

A.StateProvinceID,

A.PostalCode

FROM HumanResources.Employee Emp

INNER JOIN HumanResources.EmployeeAddress EA ON Emp.EmployeeId = EA.EmployeeId

INNER JOIN Person.Address A ON A.AddressId = EA.AddressId

You can see how quickly and easily we got this from a basic query.

What if you want to put this into stored procedure? Simple. Click the Encapsulate As New Stored Procedure. You can change the stored procedure name you want.

Renaming is the most trivial thing you have to perform in your development environment. Say for example you want to rename the Employee table to Employees. You have to check all the stored procedures, views etc. and you then you have to change them accordingly and I dont have to mention how risky it is. After all, you have to transfer this to your production environment. Not easy! The same also applies if you want to change the schema name.

SQL Refactors Smart Rename option will take care of this difficult and risky task for you. First, it will let you to enter new object name and the object schema.

Secondly, all the actions that will take place and all the objects that will be affected are listed. You can also get the resulting script from the View Script button and apply that script to the Servers that you want.

Smart Rename is available for other objects like View, Stored Procedures and functions as well.

Split Table is another valuable option you will find in this tool. If you want to normalize your table, it is not easy. You will have to create a new table. In addition, you have to rewrite all the stored procedures.Again SQL Refactor provides the Split Table option to do this with minimum effort and time.

Another feature available with this tool is its ability to summarize your scripts. When there are thousands of lines in your scripts, it is hard to read them. Summarize Script option will summarize your script so that it will improve the readability as in the following screen shot. This option is very handy when you want to debug a stored procedure which has thousands of lines.

Apart from the features mentioned, there are two other useful features within SQL Refactor. The first is highlighting unused variables. You often find that there are many unused variables in your stored procedures. There is an option in SQL Refactor called Find Unused Variables and Parameters to highlight those unused variables or parameters, so that you can delete them safely.

Qualify Object Names is the second option, which qualifies names in your script giving your code better readability.

If you do not like the initial settings for Lay Out SQL, no problem, you can change them by going through the option dialog box. There is a sample display in this dialog, so that you know what you will be getting.

Recommendation

I dont think you will have any dilemmas about the features of this wonderful product. If you think it is right tool for your environment download it at http://www.red-gate.com/dynamic/downloads/downloadform.aspx?download=sqlrefactor and experience the fantastic features of this tool with a fully functional 14-day evaluation version of Red-Gates SQL Refactor.

Finally, by combining the features of SQL Refactor and SQL Prompt - another of Red Gates tools - you will discover a totally different SQL Server editor and you will surely have a better life with coding in SQL Server 2005.

Publisher: Red-Gate

Price: Single User License: $295

Total article views: 3437 | Views in the last 30 days: 3
 
Related Articles
FORUM

Scripting options for Stored Procedures

Scripting the IF EXISTS portion of a DROP statement for Stored Procedures

ARTICLE

Change Tracking and Database Refactoring

Using change tracking in SQL Server 2008 to aid in database refactoring within an OLTP system.

ARTICLE

SQL Server Stored Procedures and SET options

SET options on stored Procedures work a bit different when compared with ad-hoc queries. These diffe...

FORUM

Refactoring

Comments posted to this topic are about the item [B]Refactoring[/B] Nice question, thanks. I voted ...

BLOG

T-SQL refactoring

Keeping your SQL objects’ naming rules during furious development is hard. New levels of information...

Tags
product reviews    
reviews    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones