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