LearnItFirst User Forum

USE master
GO
IF ( SELECT OBJECTPROPERTY(OBJECT_ID('sp_PrettySql'), 'IsProcedure')) = 1
    DROP PROC sp_PrettySql
GO
CREATE PROC sp_PrettySql (@InputString VARCHAR(MAX))
AS
SET NOCOUNT ON
/*
    Author: Scott Whigham from http://www.LearnSqlServer.com/

    Description: This script prettifies, to a certain degree, whatever SQL (like Pretty Print SQL) text you pass in.
        Right now, all it does is to UPPER case the reserved keywords for SQL Server 2005.
        To indent and do other stuff would require RegEx and bah, I don't wanna do it right now.

        Also, please don't send me examples of things that break this script - I know it doesn't fit all scenarios hehe.
        It's a simplistic, poorly-written SQL script that just handles the most common scenarios. It isn't meant to do it
        all nor is it meant to do with the $30+ SQL Pretty Print software products do. Buy those if you want things
        like indention, skipping comments, et al :)

    Misc Notes:
        I suggest that you use "Results to Text" to view the output since "Results to Grid" shows it all in one line
        Also remember that SSMS limits the amount of characters returned per column to 2048 by default (can be
            increased to only 8192) so, if you pass in lots of text, it will prettify it but you won't see it!
    
        CHAR(10) + CHAR(13) is a line break

        CHAR(13) + CHAR(10) is a line break


        Good way to test: grab a SQL 200 sp_help type proc, lowercase all the text and pass it in
            -- One gotcha: any embedded Unicode strings prefixed with N will fail the parser if you lower case it. Example:
                create proc sp_me @myparam sysname = n'sp_help' -- fails because lower case N
                create proc sp_me @myparam sysname = N'sp_help' -- succeeds

            I don't account for this in the proc because I assume (rightly or wrongly) that the code you pass in
                will compile before I prettify it.

        What about objects with multiple spaces between them, as in CREATE    PROC myProc?
            -- Not accounted for currently

        Double quoted, single quoted objects are not accounted for

        The first word of a script is always tough - it's not preceeded by a line feed/ctrl char or anything
            -- sometimes these are missed...
   
    Versions: SQL Server 2005
        This would work on SQL Server 2000/7.0 if you change VARCHAR(MAX) to VARCHAR(8000)

    Wishlit:
        * Ignore text in comments    
        * Ignore words in Object Names
   
    Creation Date: September 5, 2006

    For more scripts like this one, visit http://forums.learnsqlserver.com/codesamples.aspx
*/
DECLARE @KeywordsToUpperCase TABLE (Id INT IDENTITY(1,1), UppercaseKeyword VARCHAR(128))
DECLARE @KeywordsToLowerCase TABLE (Id INT IDENTITY(1,1), LowercaseKeyword VARCHAR(128))

-- UpperCase
INSERT @KeywordsToUpperCase
-- DML
SELECT 'SELECT' UNION ALL SELECT 'PRINT ' UNION ALL SELECT 'RAISERROR' UNION ALL
SELECT 'INSERT ' UNION ALL SELECT 'INSERT(' UNION ALL SELECT ' INSERT' UNION ALL
SELECT 'UPDATE ' UNION ALL SELECT 'UPDATE(' UNION ALL SELECT 'UPDATE' +CHAR(10) UNION ALL
SELECT 'UPDATE' +CHAR(13) UNION ALL
SELECT 'DELETE' UNION ALL
SELECT 'EXECUTE ' UNION ALL SELECT 'EXECUTE(' UNION ALL SELECT 'EXECUTE (' UNION ALL
SELECT 'EXEC ' UNION ALL SELECT 'EXEC(' UNION ALL SELECT 'EXEC (' UNION ALL
SELECT 'FROM' UNION ALL SELECT 'WHERE' UNION ALL SELECT 'GROUP BY' UNION ALL
SELECT 'ORDER BY' UNION ALL SELECT 'HAVING' UNION ALL SELECT 'INTO' UNION ALL
SELECT ' IN ' UNION ALL SELECT 'IN(' UNION ALL
SELECT 'WHEN' UNION ALL SELECT 'THEN' UNION ALL

-- END is one of the toughest
SELECT ' END,' UNION ALL -- As in a SELECT CASE WHEN ... END, NextColumn
SELECT ' END ,' UNION ALL -- As in a SELECT CASE WHEN ... END, NextColumn
SELECT ' END ' UNION ALL
SELECT ' END' + CHAR(10) UNION ALL
SELECT ' END ' + CHAR(10) UNION ALL
SELECT CHAR(10) + ' END ' UNION ALL
SELECT CHAR(10) + 'END' + CHAR(10) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(10) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(10) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(10) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(10) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(10) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(10) UNION ALL 
SELECT ' END' + CHAR(13) UNION ALL
SELECT ' END ' + CHAR(13) UNION ALL
SELECT CHAR(10) + ' END ' UNION ALL
SELECT CHAR(10) + 'END' + CHAR(13) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(10) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(13) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(13) UNION ALL
SELECT CHAR(10) + ' END' + CHAR(13) UNION ALL 

SELECT CHAR(13) + 'END' + CHAR(13) UNION ALL
SELECT CHAR(13) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(13) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(13) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(13) + ' END' + CHAR(13) UNION ALL
SELECT CHAR(13) + ' END' + CHAR(13) UNION ALL
SELECT CHAR(13) + ' END' + CHAR(13) UNION ALL 

SELECT CHAR(9) + 'END ' UNION ALL
SELECT CHAR(9) + ' END ' UNION ALL
SELECT CHAR(9) + 'END' UNION ALL
SELECT CHAR(9) + 'END' + CHAR(10) UNION ALL
SELECT CHAR(9) + 'END ' + CHAR(10) UNION ALL
SELECT CHAR(9) + ' END' + CHAR(10) UNION ALL
SELECT CHAR(9) + ' END ' + CHAR(10) UNION ALL
SELECT CHAR(9) + 'END' + CHAR(13) UNION ALL
SELECT CHAR(9) + 'END ' + CHAR(13) UNION ALL
SELECT CHAR(9) + ' END' + CHAR(13) UNION ALL
SELECT CHAR(9) + ' END ' + CHAR(13) UNION ALL
SELECT CHAR(13) + CHAR(10) + 'END' UNION ALL

-- AS is tough
SELECT ' AS ' UNION ALL
SELECT 'AS ' + CHAR(10) UNION ALL
SELECT ' AS' + CHAR(10) UNION ALL
SELECT 'AS ' + CHAR(13) UNION ALL
SELECT ' AS' + CHAR(13) UNION ALL
SELECT CHAR(10) + ' AS' + CHAR(10) UNION ALL
SELECT CHAR(10) + ' AS ' + CHAR(10) UNION ALL 
SELECT CHAR(10) + 'AS' + CHAR(10) UNION ALL
SELECT CHAR(10) + 'AS' + CHAR(13) UNION ALL
SELECT CHAR(10) + 'AS ' + CHAR(10) UNION ALL

-- Another toughie
SELECT 'BEGIN' + CHAR(9) UNION ALL SELECT 'BEGIN' + CHAR(13) UNION ALL
SELECT 'BEGIN ' UNION ALL SELECT ' BEGIN ' UNION ALL
SELECT 'BEGIN' + CHAR(10) UNION ALL
SELECT CHAR(9) + 'BEGIN' UNION ALL
SELECT CHAR(13) + CHAR(10) + 'BEGIN' UNION ALL

SELECT ' TRANSACTION' + CHAR(10) UNION ALL
SELECT ' TRAN' + CHAR(10) UNION ALL SELECT ' TRAN' + CHAR(13) UNION ALL
SELECT 'BETWEEN' UNION ALL
SELECT 'ROLLBACK' UNION ALL SELECT 'COMMIT' UNION ALL
SELECT 'WHILE' UNION ALL SELECT 'VALUES' UNION ALL
SELECT 'SUBSTRING' UNION ALL SELECT 'LTRIM' UNION ALL SELECT 'RTRIM' UNION ALL
SELECT ' NOT ' UNION ALL SELECT ' NOT(' UNION ALL
SELECT 'UPPER' UNION ALL SELECT 'LOWER' UNION ALL
SELECT 'CHARINDEX' UNION ALL SELECT 'PATINDEX' UNION ALL SELECT ' LIKE' UNION ALL
SELECT 'DATEPART' UNION ALL SELECT 'DATEADD' UNION ALL SELECT 'DATEDIFF' UNION ALL
SELECT 'DATENAME' UNION ALL SELECT 'CONVERT' UNION ALL SELECT 'CAST' UNION ALL
SELECT 'INNER JOIN' UNION ALL SELECT 'LEFT JOIN' UNION ALL SELECT 'RIGHT JOIN' UNION ALL
SELECT 'FULL JOIN' UNION ALL SELECT 'CROSS JOIN' UNION ALL
SELECT 'LEFT OUTER JOIN' UNION ALL SELECT 'RIGHT OUTER JOIN' UNION ALL SELECT 'FULL OUTER JOIN' UNION ALL

SELECT ' JOIN ' UNION ALL SELECT CHAR(10) + 'JOIN' UNION ALL
SELECT CHAR(9) + 'JOIN ' UNION ALL
SELECT CHAR(13) + CHAR(10) + 'JOIN ' UNION ALL
-- FOR XML
SELECT 'FOR XML' UNION ALL SELECT 'XML PATH' UNION ALL SELECT 'XML AUTO' UNION ALL
SELECT 'XML RAW' UNION ALL
-- DDL
SELECT CHAR(10) + 'CREATE ' UNION ALL SELECT CHAR(13) + 'CREATE ' UNION ALL
SELECT CHAR(10) + 'ALTER ' UNION ALL SELECT CHAR(13) + 'ALTER ' UNION ALL
SELECT CHAR(10) + 'DROP ' UNION ALL SELECT CHAR(13) + 'DROP ' UNION ALL
SELECT CHAR(9) + 'CREATE ' UNION ALL
SELECT CHAR(9) + 'ALTER ' UNION ALL
SELECT CHAR(9) + 'DROP ' UNION ALL

SELECT 'CREATE DATABASE' UNION ALL SELECT 'ALTER DATABASE' UNION ALL SELECT 'DROP DATABASE' UNION ALL
SELECT 'CREATE TABLE' UNION ALL SELECT 'ALTER TABLE' UNION ALL SELECT 'DROP TABLE' UNION ALL
SELECT 'CREATE PROC' UNION ALL SELECT 'ALTER PROC' UNION ALL SELECT 'DROP PROC' UNION ALL
SELECT 'CREATE PROCEDURE' UNION ALL SELECT 'ALTER PROCEDURE' UNION ALL SELECT 'DROP PROCEDURE' UNION ALL
SELECT 'CREATE FUNCTION' UNION ALL SELECT 'ALTER FUNCTION' UNION ALL SELECT 'DROP FUNCTION' UNION ALL
SELECT 'CREATE VIEW' UNION ALL SELECT 'ALTER VIEW' UNION ALL SELECT 'DROP VIEW' UNION ALL
SELECT 'CREATE TRIGGER' UNION ALL SELECT 'ALTER TRIGGER' UNION ALL SELECT 'DROP TRIGGER' UNION ALL
SELECT 'CREATE INDEX' UNION ALL SELECT 'ALTER INDEX' UNION ALL SELECT 'DROP INDEX' UNION ALL
SELECT 'DECLARE' UNION ALL
SELECT ' TABLE(' UNION ALL SELECT ' TABLE ' UNION ALL
SELECT ' PROC ' UNION ALL SELECT ' PROC(' UNION ALL
SELECT ' PROCEDURE ' UNION ALL SELECT ' PROCEDURE(' UNION ALL
SELECT 'TEXTIMAGE' UNION ALL

-- Datatypes
-- Generally only found in SQL
    SELECT 'NVARCHAR' UNION ALL SELECT 'VARCHAR' UNION ALL SELECT 'SYSNAME' UNION ALL
    SELECT 'NCHAR' UNION ALL SELECT 'SMALLDATETIME' UNION ALL  SELECT 'BIGINT' UNION ALL
    SELECT 'SMALLMONEY' UNION ALL SELECT 'VARBINARY' UNION ALL
    SELECT 'NTEXT ' UNION ALL SELECT 'NTEXT' + CHAR(10) UNION ALL
    SELECT 'SMALLINT' UNION ALL SELECT 'TINYINT' UNION ALL SELECT 'SQL_VARIANT' UNION ALL
    SELECT 'FLOAT' UNION ALL SELECT 'DECIMAL' UNION ALL SELECT 'NUMERIC' UNION ALL
    
    -- Found in common words    
    SELECT ' CHAR ' UNION ALL SELECT ' CHAR(' UNION ALL SELECT ' CHAR (' UNION ALL
        SELECT CHAR(9) + 'CHAR ' UNION ALL SELECT CHAR(9) + 'CHAR(' UNION ALL
        SELECT CHAR(9) + 'CHAR (' UNION ALL
    SELECT ' MONEY ' UNION ALL SELECT ' MONEY,' UNION ALL SELECT ' MONEY' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'MONEY' UNION ALL
    SELECT ' TEXT ' UNION ALL SELECT ' TEXT,' UNION ALL SELECT ' TEXT' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'TEXT' UNION ALL
    SELECT ' BIT ' UNION ALL SELECT ' BIT,' UNION ALL SELECT ' BIT' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'BIT' UNION ALL
    SELECT ' DATETIME ' UNION ALL SELECT ' DATETIME,' UNION ALL SELECT ' DATETIME' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'DATETIME' UNION ALL
    SELECT ' BINARY ' UNION ALL SELECT ' BINARY,' UNION ALL SELECT ' BINARY' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'BINARY' UNION ALL
    SELECT ' INTEGER ' UNION ALL SELECT ' INTEGER,' UNION ALL SELECT ' INTEGER' +CHAR(10) UNION ALL
        SELECT CHAR(9) + 'INTEGER' UNION ALL
    SELECT ' IMAGE ' UNION ALL SELECT ' IMAGE,' UNION ALL SELECT ' IMAGE' +CHAR(10) UNION ALL    
        SELECT CHAR(9) + 'IMAGE' UNION ALL

-- INT is very tough so I cheated and put in some commonly used phrases
SELECT ' INT' +CHAR(10) UNION ALL SELECT ' INT' +CHAR(13) UNION ALL
SELECT ' INT,' UNION ALL SELECT ' INT ,' UNION ALL
SELECT CHAR(9) +'INT ' UNION ALL SELECT CHAR(9) +'INT,' UNION ALL
SELECT 'AS INT' UNION ALL SELECT 'AS [INT]' UNION ALL
SELECT 'INT NOT NULL' UNION ALL SELECT 'INT NULL' UNION ALL
SELECT ' INT)' UNION ALL SELECT 'INT IDENTITY' UNION ALL
SELECT ' INT=' UNION ALL SELECT ' INT =' UNION ALL
-- Aggs
SELECT 'MAX(' UNION ALL SELECT 'MAX (' UNION ALL
SELECT 'AVG(' UNION ALL SELECT 'AVG (' UNION ALL
SELECT 'MIN(' UNION ALL SELECT 'MIN (' UNION ALL
SELECT 'SUM(' UNION ALL SELECT 'SUM (' UNION ALL
SELECT 'COUNT(' UNION ALL SELECT 'COUNT (' UNION ALL SELECT 'COUNT(*)' UNION ALL
SELECT 'GROUPING(' UNION ALL SELECT 'GROUPING (' UNION ALL 
-- Properties
SELECT ' IDENTITY' UNION ALL SELECT 'NOT NULL' UNION ALL
SELECT ' NULL ' UNION ALL SELECT ' NULL' + CHAR(13) UNION ALL
SELECT '=NULL' UNION ALL SELECT '= NULL' UNION ALL
SELECT 'NULL)' UNION ALL SELECT 'NULL )' UNION ALL
-- Constraints
SELECT ' DEFAULT ' UNION ALL SELECT ' DEFAULT(' UNION ALL
SELECT CHAR(9) + 'DEFAULT ' UNION ALL SELECT CHAR(9) + 'DEFAULT(' UNION ALL
SELECT CHAR(10) + 'DEFAULT ' UNION ALL SELECT CHAR(10) + 'DEFAULT(' UNION ALL
SELECT CHAR(13) + 'DEFAULT ' UNION ALL SELECT CHAR(13) + 'DEFAULT(' UNION ALL

SELECT ' CHECK ' UNION ALL SELECT CHAR(9) + 'CHECK ' UNION ALL
SELECT CHAR(10) + 'CHECK ' UNION ALL SELECT CHAR(13) + 'CHECK ' UNION ALL
SELECT 'NOCHECK' UNION ALL
SELECT 'CONSTRAINT' UNION ALL
SELECT 'PRIMARY KEY' UNION ALL SELECT 'FOREIGN KEY' UNION ALL SELECT 'UNIQUE' UNION ALL
SELECT ' REFERENCES' UNION ALL SELECT 'REFERENCES ' UNION ALL
-- Indexes
SELECT ' INDEX ' UNION ALL SELECT ' INDEX' UNION ALL
SELECT 'NONCLUSTERED' UNION ALL SELECT 'NONCLUSTERED' UNION ALL
-- Functions
SELECT 'QUOTENAME' UNION ALL SELECT 'REPLICATE' UNION ALL
SELECT 'LEFT(' UNION ALL SELECT 'RIGHT(' UNION ALL
SELECT 'OBJECTPROPERTY' UNION ALL SELECT 'LEN(' UNION ALL SELECT 'DATALENGTH' UNION ALL
SELECT 'OBJECT_ID(' UNION ALL SELECT 'OBJECT_ID (' UNION ALL
SELECT 'OBJECT_NAME(' UNION ALL SELECT 'OBJECT_NAME (' UNION ALL
SELECT 'SCHEMA_ID(' UNION ALL SELECT 'SCHEMA_ID (' UNION ALL
SELECT 'SCHEMA_NAME(' UNION ALL SELECT 'SCHEMA_NAME (' UNION ALL
SELECT 'PARSENAME' UNION ALL SELECT 'COLUMNPROPERTY' UNION ALL
SELECT 'USER_NAME(' UNION ALL SELECT 'USER_NAME (' UNION ALL
SELECT 'SUSER_SID' UNION ALL
SELECT 'SUSER_SNAME' UNION ALL SELECT 'TYPEPROPERTY' UNION ALL
SELECT 'IDENT_SEED' UNION ALL SELECT 'IDENT_CURRENT' UNION ALL SELECT 'IDENT_INCR' UNION ALL
SELECT 'SCOPE_IDENTITY' UNION ALL SELECT 'ISNUMERIC' UNION ALL SELECT 'ISDATE' UNION ALL
SELECT 'DB_ID(' UNION ALL SELECT 'DB_ID (' UNION ALL
SELECT 'DB_NAME(' UNION ALL SELECT 'DB_NAME (' UNION ALL
SELECT 'HOSTNAME(' UNION ALL SELECT 'HOSTNAME (' UNION ALL
SELECT 'APPNAME(' UNION ALL SELECT 'APPNAME (' UNION ALL
SELECT 'GETDATE' UNION ALL SELECT 'UTCDATE' UNION ALL
SELECT 'ERROR_LINE' UNION ALL SELECT 'ERROR_NUMBER' UNION ALL SELECT 'ERROR_MESSAGE' UNION ALL
SELECT 'ERROR_PROCEDURE' UNION ALL SELECT 'ERROR_SEVERITY' UNION ALL SELECT 'ERROR_STATE' UNION ALL
SELECT 'BEGIN TRY' UNION ALL SELECT 'END TRY' UNION ALL
SELECT 'BEGIN CATCH' UNION ALL SELECT 'END CATCH' UNION ALL
SELECT 'NEWID' UNION ALL SELECT 'ORIGINAL_LOGIN' UNION ALL SELECT 'XACT_STATE' UNION ALL
SELECT 'COLLATIONPROPERTY' UNION ALL SELECT 'SERVERPROPERTY' UNION ALL
SELECT 'DATABASEPROPERTY' UNION ALL SELECT 'DATABASEPROPERTYEX' UNION ALL
SELECT 'HAS_DBACCESS' UNION ALL

-- Misc
SELECT 'USE [' UNION ALL
SELECT CHAR(10) + 'USE ' UNION ALL
SELECT CHAR(13) + 'USE ' UNION ALL
SELECT CHAR(9) + 'USE ' UNION ALL
SELECT ' USE ' UNION ALL

SELECT 'PIVOT' UNION ALL SELECT 'UNPIVOT' UNION ALL SELECT 'ROW_NUMBER' UNION ALL
SELECT 'DENSE_RANK' UNION ALL SELECT 'NTILE' UNION ALL
SELECT 'EXCEPT' UNION ALL SELECT 'INTERSECT' UNION ALL SELECT '' UNION ALL SELECT '' UNION ALL
SELECT CHAR(10) + CHAR(13) + 'WITH' UNION ALL

-- Top is tough too!
SELECT ' TOP' UNION ALL
SELECT 'DISTINCT' UNION ALL

-- FOR is tough
SELECT CHAR(10) + CHAR(13) + 'FOR' UNION ALL

SELECT ' CURSOR' UNION ALL SELECT 'FETCH ' UNION ALL SELECT 'CHECKPOINT' UNION ALL
SELECT CHAR(10) + CHAR(13) + 'OPEN' UNION ALL SELECT CHAR(13) + CHAR(10) + 'OPEN' UNION ALL
SELECT CHAR(9) + 'OPEN' UNION ALL
SELECT 'CURRENT OF' UNION ALL
SELECT 'DEALLOCATE' UNION ALL SELECT 'FETCH_STATUS' UNION ALL
SELECT 'WITH NOLOCK' UNION ALL SELECT 'WITH(NOLOCK' UNION ALL SELECT 'WITH (NOLOCK' UNION ALL

SELECT 'COLLATE' UNION ALL SELECT 'BULK INSERT' UNION ALL
SELECT 'TRUNCATE TABLE' UNION ALL SELECT 'GOTO' UNION ALL
SELECT 'ON [PRIMARY]' UNION ALL SELECT 'ON PRIMARY' UNION ALL
SELECT 'ADD CONSTRAINT' UNION ALL SELECT 'DROP COLUMN' UNION ALL SELECT 'ALTER COLUMN' UNION ALL
SELECT 'ISNULL' UNION ALL SELECT 'IS NOT NULL' UNION ALL SELECT 'IS NULL' UNION ALL
SELECT 'COALESCE' UNION ALL
SELECT 'NULLIF' UNION ALL SELECT 'EXISTS' UNION ALL
SELECT ' CASE' UNION ALL SELECT CHAR(9) + 'CASE' UNION ALL
-- DBCC
SELECT 'DBCC' UNION ALL SELECT 'SQLPERF' UNION ALL SELECT 'LOGSPACE' UNION ALL
SELECT 'DBREINDEX' UNION ALL SELECT 'SHRINKDATABASE' UNION ALL SELECT 'SHRINKFILE' UNION ALL
-- And is tough
SELECT ' AND' UNION ALL SELECT CHAR(9) + 'AND ' UNION ALL
-- OR is tough
SELECT ' OR ' UNION ALL
SELECT CHAR(9) + 'OR ' UNION ALL
SELECT CHAR(10) + 'OR' UNION ALL
SELECT CHAR(13) + 'OR' UNION ALL
SELECT CHAR(10) + 'OR' + CHAR(10) UNION ALL
SELECT CHAR(13) + 'OR' + CHAR(13) UNION ALL

SELECT ' IF' UNION ALL SELECT CHAR(9) + 'IF ' UNION ALL SELECT CHAR(10) + 'IF' UNION ALL
SELECT 'IF EXISTS' UNION ALL SELECT 'IF(' UNION ALL SELECT 'IF (' UNION ALL
SELECT 'IF @' UNION ALL
SELECT 'ELSE' UNION ALL
SELECT ' OUTPUT)' UNION ALL SELECT ' OUTPUT' + CHAR(10) UNION ALL SELECT ' OUTPUT' + CHAR(13) UNION ALL
    SELECT ' OUTPUT,' UNION ALL SELECT ' OUTPUT ,' UNION ALL
SELECT ' OUT)' UNION ALL SELECT ' OUT' + CHAR(10) UNION ALL SELECT ' OUT' + CHAR(13) UNION ALL
    SELECT ' OUT,' UNION ALL SELECT ' OUT ,' UNION ALL

SELECT CHAR(10) + 'RETURN' UNION ALL SELECT CHAR(9) + 'RETURN' UNION ALL SELECT ' RETURN' UNION ALL
SELECT ' UNION' UNION ALL SELECT CHAR(10) + 'UNION' UNION ALL SELECT 'UNION ALL' UNION ALL
-- Settings
SELECT 'ANSI_WARNINGS' UNION ALL SELECT 'ANSI_PADDING' UNION ALL SELECT 'ARITHABORT' UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL' UNION ALL SELECT 'ROUNDABORT' UNION ALL
SELECT 'ISOLATION_LEVEL' UNION ALL SELECT 'QUOTED_IDENTIFIER' UNION ALL SELECT 'ANSI_NULLS' UNION ALL
SELECT 'SET ' UNION ALL SELECT 'IDENTITY_INSERT' UNION ALL
SELECT 'NOCOUNT' UNION ALL
SELECT 'SET NOCOUNT ON' UNION ALL SELECT 'SET NOCOUNT OFF' UNION ALL -- sometimes it's just easier to write the whole statement!

SELECT ' ON ' UNION ALL SELECT ' ON(' UNION ALL SELECT CHAR(10) + 'ON(' UNION ALL
SELECT ' ON' + CHAR(13) UNION ALL SELECT CHAR(9) + 'ON ' UNION ALL
SELECT CHAR(13) + CHAR(10) + 'ON ' UNION ALL

SELECT ' OFF ' UNION ALL SELECT ' OFF' + CHAR(13) UNION ALL

SELECT 'XACT_ABORT' UNION ALL SELECT 'STATISTICS TIME' UNION ALL
SELECT 'STATISTICS IO' UNION ALL SELECT 'SHOWPLAN' UNION ALL
SELECT CHAR(13) + CHAR(10) + 'GO' UNION ALL
-- Global Variables
SELECT '@@ERROR' UNION ALL SELECT '@@TRANCOUNT' UNION ALL SELECT '@@ROWCOUNT' UNION ALL
SELECT '@@SPID' UNION ALL SELECT '@@IDENTITY'

INSERT @KeywordsToLowerCase
SELECT 'dbo'

/****************************************************************************************************************
    For each row in the above, loop through and replace the keywords in @InputString
****************************************************************************************************************/
DECLARE @NumberOfKeywords INT, @CurrentId INT, @Keyword VARCHAR(128)

/****************************************************************************************************************
    Upper case
****************************************************************************************************************/
SELECT @NumberOfKeywords = COUNT(*), @CurrentId = MIN(Id) FROM @KeywordsToUpperCase

WHILE @CurrentId <= @NumberOfKeywords
    BEGIN
        SELECT @Keyword = UppercaseKeyword FROM @KeywordsToUpperCase WHERE Id = @CurrentId
        SET @InputString = REPLACE(@InputString, @Keyword, @Keyword)

        -- What about keywords with [] around them, as in [int], [nvarchar]? They won't have spaces around them so LTRIM()
        SET @InputString = REPLACE(@InputString, '['+LTRIM(RTRIM(@Keyword))+']', '['+LTRIM(RTRIM(@Keyword))+']')
        
        SET @CurrentId = @CurrentId + 1
    END

/****************************************************************************************************************
    Lower case
****************************************************************************************************************/
SELECT @NumberOfKeywords = COUNT(*), @CurrentId = MIN(Id) FROM @KeywordsToLowerCase

WHILE @CurrentId <= @NumberOfKeywords
    BEGIN
        SELECT @Keyword = LowercaseKeyword FROM @KeywordsToLowerCase WHERE Id = @CurrentId
        SET @InputString = REPLACE(@InputString, @Keyword, @Keyword)

        -- What about keywords with [] around them, as in [int], [nvarchar]? They won't have spaces around them so LTRIM()
        SET @InputString = REPLACE(@InputString, '['+LTRIM(RTRIM(@Keyword))+']', '['+LTRIM(RTRIM(@Keyword))+']')
        
        SET @CurrentId = @CurrentId + 1
    END

/****************************************************************************************************************
    Remove extra line breaks
****************************************************************************************************************/
-- Change triple line breaks to singles
SET @InputString = REPLACE(@InputString, CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+CHAR(13), CHAR(10)+CHAR(13))

SELECT @InputString -- Do not put a column header since it gets sent back as text
GO
-- Test it:
EXEC sp_PrettySql 'select firstName as fName,
LastName As Surname froM MyTable order by fNAme'