The other day I was given a truly horribly formatted piece of SQL. Something a bit like this only worse, and with plenty of dynamic SQL.

CREATE TRIGGER [Sales].[iduSalesOrderDetail] ON [Sales].[SalesOrderDetail] 

AFTER INSERT, DELETE, UPDATE AS 

Begin

    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 

        RETURN;

    SET nocount on;

    begin try
-- If inserting or updating these columns
IF update([ProductID]) OR UPDATE([OrderQty]) or Update([UnitPrice]) OR update([UnitPriceDiscount]) 
-- Insert record into TransactionHistory
begin
    INSERT into [Production].[TransactionHistory]
                ([ProductID],[ReferenceOrderID]

    ,[ReferenceOrderLineID]

    ,[TransactionType]

    ,[TransactionDate]

                ,[Quantity]

                ,[ActualCost])

            SELECT 

                inserted.[ProductID]        ,inserted.[SalesOrderID]

                ,inserted.[SalesOrderDetailID]

                ,'S',GETDATE()                ,inserted.[OrderQty]

                ,inserted.[UnitPrice]

FROM inserted 

INNER join [Sales].[SalesOrderHeader] 

ON inserted.[SalesOrderID] = [Sales].[SalesOrderHeader].[SalesOrderID];
            UPDATE [Person].[Person] 

            SET [Demographics].modify('declare default element namespace 

                "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey"; 

                replace value of (/IndividualSurvey/TotalPurchaseYTD)[1] 

                with data(/IndividualSurvey/TotalPurchaseYTD)[1] + sql:column ("inserted.LineTotal")')   from inserted 
                INNER JOIN [Sales].[SalesOrderHeader] AS SOH

ON inserted.[SalesOrderID] = SOH.[SalesOrderID] INNER JOIN [Sales].[Customer] AS C

                ON SOH.[CustomerID] = C.[CustomerID]
    WHERE C.[PersonID] = [Person].[Person].[BusinessEntityID];

        END;
        -- Update SubTotal in SalesOrderHeader record. Note that this causes the 

        -- SalesOrderHeader trigger to fire which will update the RevisionNumber.

        UPDATE [Sales].[SalesOrderHeader]

I have a hard time working with code that’s so badly formatted I can’t read it. Normally I’d just format it myself, but this was a good dozen pages long. So what did I do? I ran to twitter screaming for help.

And I got a lot of responses! Here were the recommendations followed by who recommended them:

• TSQLTidy

  Rob Volk (b/t)

  Buck Woody (b/t)

• PoorSQL

  Randolph West (b/t)

  Tim Radney (b/t)

  Big Mike (t)

• Apex SQL Refactor

  Kendal Van Dyke (b/t)

  Michael Sosis (t)

• Operations Studio

  Gavin Draper (b/t)

• SQL Format

  Kristy Mishra (t)

  Aalamjeet Singh Rangi (b/t)

• The Simple-Talk Code Prettifier

  Jen McCown (b/t)

• SQLInForm
• SQL Formatter

  Aalamjeet Singh Rangi (b/t)

I haven’t tested most of these but from what I understand none of them (for obvious reasons) will handle dynamic SQL and the ones I tested won’t handle the multi-line breaks. For the multi-line breaks I just did a regex search and replace, then used the formatter.

Now, as I was writing this Brent Erik Darling (b) wrote a similar post so I’m including it for completeness. Quick tips for debugging large stored procedures – See tip 1