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