A Review of SQL Refactor

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.

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:

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.

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.

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.

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.

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.

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.