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