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) with data(/IndividualSurvey/TotalPurchaseYTD) + 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:
Big Mike (t)
- Apex SQL Refactor
Michael Sosis (t)
- Operations Studio
- SQL Format
Kristy Mishra (t)
- The Simple-Talk Code Prettifier
- SQL Formatter
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