Is There a Tool for Refactoring SQL, a Bit Like a Resharper for SQL

Is there a tool for refactoring SQL, a bit like a ReSharper for SQL

JetBrains have just announced 0xDBE - a new IDE for SQL machines.

It's build on the IntelliJ platform, so should have many of the same shortcuts you get in the JetBrains suite of products.

http://www.jetbrains.com/dbe/

Can a database be refactored in SQL Server?

If a tool like SSDT does not work for you for whatever reason, you could do this manually at your own pace. Sometimes a "click one button and go home" approach is actually not what you want.

You could start with introducing synonyms, e.g.

CREATE SYNONYM dbo.Requests FOR dbo.SheetMetalRequest;

Now you can refactor your code in pieces, pointing each reference to the new name. When you are confident that you have captured all references (and this won't be easy even with a tool like SSDT, since it can't see code outside of the database - or even code in dynamic SQL inside the current database), you can drop the synonym and rename the table:

BEGIN TRANSACTION;
    DROP SYNONYM dbo.Requests;
    EXEC sp_rename 'dbo.SheetMetalRequest', 'Requests', 'OBJECT';
COMMIT TRANSACTION

(You'll also want to update all of your modules with sp_refreshsqlmodule in a loop, twice, to make sure you've completely corrected dependencies.)

This would allow you to update different portions of your code (not just views / stored procedures but also middle-tier classes and even front end stuff) to reference the right names, and not have to do it all at once.

Oracle DB (PL/SQL) Refactoring Tools

As it happens the "D" in TOAD stands Developer not DBA. In fact many DBAs anathematise TOAD and other such tools.

You are out of luck. As far as I know there are no tools for refactoring PL/SQL. The root problem is that refactoring as a concept comes from the OOP paradigm, and PL/SQL is not object oriented. It does not support inheritance or polymorphism (*). This means that many of the techniques which underpin classical refactoring practice (say as defined by Fowler) - abstraction, interfaces, etc - have no analogue in PL/SQL.

The corollary of this is that people who are used to having refactoring as part of their conceptual toolbox tend to avoid programming with PL/SQL. I once got into a heated debate on the TDD list over this. The upshot being that even developers who need and appreciated the virtues of PL/SQL would rather develop in languages with better tool support, clause #1 of the Agile Manifesto notwithstanding.

The most important tool for refactoring is automated unit testing. Although TOAD does not (I think) have integrated unit testing, the next release of Oracle SQL Developer will. There are also standalone unit test tools. I recently mentioned a couple of them in another SO thread.

In terms of refactoring PL/SQL to match changes in the database, arguably most interaction with tables ought to be underaqtken by generated table APIs rather than being embedded in transactional PL/SQL. In this happy realm there is no need for refactoring tools, we just need to re-generate the relevant APIs. The post I linked to above also mentions QCGU, a tool which can do this. Of course, when we have a PL/SQL codebase which isn't organised in such a fashion then life is harder. You won't be surprised to learn that there isn't a lot of tool support for implementing Feathers's WELC in PL/SQL.

(*) Yes, I know Oracle has Types but they are (a) SQL not PL/SQL and (b) how many people out there are actually building APIs using them?

Does any one know of any APEX refactoring tools?

As yet there are no tools that offer refactoring support for the Apex language.

Is there a working C++ refactoring tool?

I find Visual Assist X with Visual Studio very useful. Another choice is Refactor for C++.

Please help transform Tsql implicit joins into explicit ones

I think it should be something like this:

FROM AB
JOIN CD ON AB.VU_code = CD.VU_code
JOIN IJ ON IJ.EF_id = AB.EF_id AND IJ.ZY_id = AB.ZY_id AND IJ.IJ_id = AB.IJ_id
JOIN EF ON EF.EF_id = IJ.EF_id AND EF.ZY_id = IJ.ZY_id
JOIN GH ON EF.XW_id = GH.GH_id
JOIN KL ON IJ.PO_id = KL.PO_id
WHERE
    IJ.IJ_id = @IJ_id AND
    IJ.TS > 0 AND
    IJ.RQ = 0 AND
    EF.RQ = 0 AND
    AB.RQ = 0

I have tried to arrange the tables such that the following rules hold:

• Every join condition mentions the new table that it joining on one side.
• No table is mentioned in a join condition if that table has not been joined yet.
• Conditions where one of the operands is a constant are left as a WHERE condition.

The last rule is a difficult one - it is not possible to tell from your mangled names whether a condition ought to be part of a join or part of the where clause. Both will give the same result for an INNER JOIN. Whether the condition should be part of the join or part of the where clause depends on the semantics of the relationship between the tables.

You need to consider each condition on a case-by-case basis:

• Does it define the relationship between the two tables? Put it in the JOIN.
• Is it a filter on the results? Put it in the WHERE clause.

Some guidelines:

• A condition that includes a parameter from the user is unlikely to be something that should be moved to a join.
• Inequalities are not usually found in join conditions.

---

Related Topics