Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

SQL Refactor Rocks!

I am creating a test database this afternoon that I can use with my integration tests, and while doing so, I tried out SQL Refactor.

 

Cleaning Up a Simple Stored Procedure

I had a very simple stored procedure I needed to write at first that I lazily started like this:

 

CREATE PROCEDURE [dbo].[GetCustomerByCustomerId]
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * FROM Customers WHERE CustomerId = @CustomerId
END

 

Honestly, I did not feel like listing the individual column names in the select statement :), but since SQL Refactor does it with 2 keystrokes, what the heck:

 

SELECT Customers.CustomerId, Customers.FirstName, Customers.LastName, Customers.EmailAddress,
Customers.CreateDate FROM Customers WHERE CustomerId = @CustomerId

 

Another 2 keystrokes and I qualified the Customers Table (dbo.), which could be a pain if I had a lot of joins and a large stored procedure:

 

SELECT Customers.CustomerId, Customers.FirstName, Customers.LastName, Customers.EmailAddress,
Customers.CreateDate FROM dbo.Customers WHERE CustomerId = @CustomerId

 

Another couple of keystrokes and I had it looking mighty nice:

 

CREATE PROCEDURE [dbo].[GetCustomerByCustomerId] @CustomerId int
AS 
    BEGIN
        SET NOCOUNT ON ;

        SELECT  Customers.CustomerId,
                Customers.FirstName,
                Customers.LastName,
                Customers.EmailAddress,
                Customers.CreateDate
        FROM    dbo.Customers
        WHERE   Customers.CustomerId = @CustomerId
    END

 

Even with this being a tiny CRUD stored procedure, SQL Refactor saved me a lot of typing.

 

Smart Renaming

I was pumped about the keystroke savings alone, but the smart renaming is absolutely awesome! Now for kicks, I wanted to rename my Customers Table to Customers2 just to see if it would alter my stored procedure appropriately. Here is the summary of things to happen:

 

 

 

 Sweet! Here is how the stored procedure rename looks in the script:

 

ALTER PROCEDURE dbo.GetCustomerByCustomerId @CustomerId int
AS 
    BEGIN
        SET NOCOUNT ON ;

        SELECT  Customers2.CustomerId,
                Customers2.FirstName,
                Customers2.LastName,
                Customers2.EmailAddress,
                Customers2.CreateDate
        FROM    dbo.Customers2
        WHERE   Customers2.CustomerId = @CustomerId
    END

 

Awesome! Simply awesome! I will be testing SQL Refactor out further this evening.

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

2 Responses to SQL Refactor Rocks!

  1. johnpapa says:

    It is a very nice tool. A little pricey at $295, though. I think for its features it should be $95 or $150.

    I’ve enjoyed both SQL Refactor and SQL Prompt from Red Gate. They really make writing and formatting SQL much nicer. I despise looking at procs that look like my dog wrote them: No indenting, hard to read, no organization at all. We don’t write code like that so why SQL?

  2. Mitch Wheat says:

    Looks great, David. Thanks for pointing this out.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>