+972 52-548-6969
SQL Refactor
So I got the beta of SQL Refactor, opened SQL Management Studio and liked what I saw:
The first thing that I noticed, more important than anything else, is that it has a SQL Beautifier. This is the first I have seen this for T-SQL (there seems to be a lot for Oracle, though), and the first where the beautifier is integrated directly into SQL Management studio.
Why is this important? Well, check this out (from Northwind):
ALTER procedure [dbo].[Employee Sales by Country]
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName, Employees.FirstName, Orders.ShippedDate, Orders.OrderID, "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN
(Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
And after applying the formatting:
ALTER procedure [dbo].[Employee Sales by Country]
@Beginning_Date DateTime,
@Ending_Date DateTime
AS
SELECT
Employees.Country,
Employees.LastName,
Employees.FirstName,
Orders.ShippedDate,
Orders.OrderID,
"Order Subtotals".Subtotal AS SaleAmount
FROM
Employees
INNER JOIN
(
Orders INNER JOIN
"Order Subtotals"
ON Orders.OrderID = "Order Subtotals".OrderID
)
ON Employees.EmployeeID = Orders.EmployeeID
WHERE
Orders.ShippedDate Between @Beginning_Date And @Ending_Date
I can actually read that. And the formatting is configuration enough that in 30 seconds I got just the way I wanted. Okay, so I am gushing over a code formatter, not very exciting, until you realize that I have to read thousnads of SQL Statements that were just off. The other stuff on the menu looks very interesting as well.
Expand wildcards means moving from "select * from foo" to a proper statement. Qualify names seems to work on the schema level, although I expected it to work on the columns as well. (Meaning that it turned Employees to [dbo].[Employees], but didn't changed FirstName to Employees.FirstName).
Moving right along, I looked at the script summarizer:
It can give a overview of a script, and take you directly into parts of it. Then you can use the Encapsulate Stored Procedure to extract stuff out. I like the way the wizards for the actions are setup, so I can move back and forth between them, and the commentry is excellent (the screen shot below if from split table refactoring):
Finishing thoughts:
- It generate scripts that would modify the DB, and the whole eperiance is... respectful is not quite the word I am looking for here. Too often I see a tool that is dumbing down ideas, and I get annoyed. Check out this UI, I don't think that I can explain it better now (now being 3AM here):
- I wonder how this would deal with a big DB (4000+ tables, hundreds of SPs, views, etc) when working on it remotedly.
- Missing finishing touches:
- There is no integration with right mouse click on the text editor, and it is annoying.
- No accesselrator key for the menu, more annoying.
It looks very nice, and as you can see, what excites me the most is probably what the guys at Red Gate spent the least time on :-). When I hear the term Database Refactoring, I think about moving tables around, Add Column, Move Column, etc. I didn't consider that refactoring in this sense will be improvements to the scripts themselves. In retrospect, that seems very obvious.
I know that already SQL Prompt has changed the way I work with databases, and I think that this has a similar potential. I know of one thing that I would like to put it to use already. Rename refactor on all the tables in the database, From Taarich to Date. I work with databases quite a bit, although I am not a DBA, and from a ten minute run, I know of several places where I would want to know that. (I would have given a lot to have this nine months ago, when I was doing the Big SQL Project).
Comments
Comment preview