Navigation for dotNetTips.com - Formatting T-SQL

As you might know I'm really picky when it comes to formatting code... heck, I event wrote a book on it! This even extends to T-SQL. I have worked at companies that had stored procedures that were formatted so poorly that I could not follow it at all. Thankfully I found a very cool web site called Instant SQL Formatter that does all the work for me... instantally! It will format T-SQL like this (generated by SQL Server Management Studio):

USE [Acme]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].EventLog_DeleteOld') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].EventLog_DeleteOld
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].EventLog_DeleteOld

AS
BEGIN
 DELETE FROM EventLog WHERE ([TimeStamp] < GETDATE() - 14)
END
GO

To this:

USE [Acme]

GO

IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].EventLog_DeleteOld')
                  AND TYPE IN (N'P',N'PC'))
  DROP PROCEDURE [dbo].eventlog_deleteold

GO

SET ansi_nulls  ON

GO

SET quoted_identifier  ON

GO

CREATE PROCEDURE [dbo].Eventlog_deleteold
AS
  BEGIN
    DELETE FROM eventlog
    WHERE       ([TimeStamp] < Getdate() - 14)
  END

GO

Very nice! There are lots of options (the T-SQL was formatted with just the defaults).

Tip Submitted By: David McCarter