Trim Trailing Blanks in SQL Queries

/***************************************************************************************************
Written by:			Jesse McLain
Purpose:			Given a SQL SELECT statement, this spd will run it, saving the results in a temp
					table, and query the temp table for the max size of the varchar columns. It will
					then rerun the query, and limit the size of the output of those varchar columns 
					to the max size it discovered. Typically this spd will be used to save metadata
					in an ASCII text report.
Input Parameters:	see below
Output Parameters:	none
Called By:			user
***************************************************************************************************/
CREATE PROCEDURE [dbo].[spd_Tool_Reformat_Query_Results]
(
	@sql varchar(8000),			-- the SQL SELECT statement to generate the results
	@print_sql char(1) = 'N'	-- 'Y' if user wants to see SQL used to reformat results
)
AS


SET NOCOUNT ON
SET ANSI_WARNINGS OFF


IF @sql = 'help' OR @sql = '' OR @sql IS NULL OR LEFT(@sql, 6) <> 'SELECT'
BEGIN
	PRINT 'PROCEDURE [dbo].[spd_Tool_Reformat_Query_Results] - parameters:'
	PRINT '	@sql varchar(8000)			-- the SQL SELECT statement to generate the results'
	PRINT '	@print_sql char(1) = ''N''	-- ''Y'' if user wants to see SQL used to reformat results'

	RETURN
END


-- normalize incoming SQL statement; make sure that the only white space is space char:
SET @sql = REPLACE(@sql, CHAR(13), ' ')
SET @sql = REPLACE(@sql, CHAR(10), ' ')
SET @sql = REPLACE(@sql, CHAR(9), ' ')
WHILE CHARINDEX('  ', @sql) > 0 SET @sql = LTRIM(RTRIM(REPLACE(@sql, '  ', ' ')))		-- replace double space with single


DECLARE @fnd smallint
DECLARE @fnd2 smallint
DECLARE @sql_target varchar(100)
SET @sql_target = ''
DECLARE @tmp varchar(8000)


-- we need to insert an "INTO" clause into the SQL:
SET @fnd = CHARINDEX(' INTO ', @sql)
IF @fnd > 0
BEGIN
	-- if there already is one, then we replace it:
	SET @tmp = SUBSTRING(@sql, @fnd + 6, DATALENGTH(@sql) - @fnd)
	SET @fnd2 = CHARINDEX(' ', @tmp)
	IF @fnd2 > 0
	BEGIN
		SET @sql_target = LEFT(@tmp, @fnd2 - 1)
		SET @sql = REPLACE(@sql, ' INTO ' + @sql_target, ' INTO ##Tmp_Query_Results')
	END
END
ELSE	-- otherwise, the "INTO" clause goes before the "FROM" clause:
BEGIN
	-- if there's not already an 'INTO' clause, then we'll find the 'FROM' clause and
	-- insert our new 'INTO' just before it:
	SET @fnd = CHARINDEX(' FROM ', @sql)
	IF @fnd > 0
	BEGIN
		SET @sql = STUFF(@sql, @fnd, 0, ' INTO ##Tmp_Query_Results')
	END
END


IF EXISTS(SELECT 1 FROM TempDb.dbo.Sysobjects WHERE Name = '##Tmp_Query_Results' AND Type = 'U') DROP TABLE ##Tmp_Query_Results
EXEC(@sql)

CREATE TABLE #Tmp_Col_Max_Size (Col_Max_Size int)

DECLARE @ColName varchar(200)
DECLARE @ColType varchar(20)
DECLARE @ColLength smallint
DECLARE @Col_Max_Size int

DECLARE Tmp_Table_Structure_Cursor CURSOR FOR
	SELECT 
		ColName = C.Name,
		ColType = T.Name,
		ColLength = C.Length
	FROM TempDb.dbo.SysColumns C 
	JOIN TempDb.dbo.SysObjects O ON O.Id = C.Id
	JOIN TempDb.dbo.SysTypes T ON T.XType = C.XType
	WHERE O.Name = '##Tmp_Query_Results'
	AND T.Name <> 'sysname'
	ORDER BY C.ColOrder

OPEN Tmp_Table_Structure_Cursor 

DECLARE @sql_refmt varchar(8000)
SET @sql_refmt = 'SELECT ' + CHAR(13) + CHAR(10) + CHAR(9)

FETCH NEXT FROM Tmp_Table_Structure_Cursor INTO @ColName , @ColType, @ColLength
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @ColType IN ('char', 'varchar', 'nchar', 'nvarchar')
	BEGIN
		TRUNCATE TABLE #Tmp_Col_Max_Size
		IF @ColType IN ('nchar', 'nvarchar')
			SET @sql = 'SELECT MAX(DATALENGTH(CONVERT(varchar(8000), ' + @ColName + '))) FROM ##Tmp_Query_Results'
		ELSE
			SET @sql = 'SELECT MAX(DATALENGTH(' + @ColName + ')) FROM ##Tmp_Query_Results'
		INSERT INTO #Tmp_Col_Max_Size EXEC(@sql)
		SELECT @Col_Max_Size = Col_Max_Size FROM #Tmp_Col_Max_Size

		IF @Col_Max_Size IS NULL
			SET @sql_refmt = @sql_refmt + @ColName + ' = LEFT(' + @ColName + ', 4), '
		ELSE
			SET @sql_refmt = @sql_refmt + @ColName + ' = LEFT(' + @ColName + ', ' + LTRIM(STR(@Col_Max_Size)) + '), '
	END
	ELSE
		SET @sql_refmt = @sql_refmt + @ColName + ', '

	SET @sql_refmt = @sql_refmt + CHAR(13) + CHAR(10) + CHAR(9)

	FETCH NEXT FROM Tmp_Table_Structure_Cursor INTO @ColName , @ColType, @ColLength
END

CLOSE Tmp_Table_Structure_Cursor
DEALLOCATE Tmp_Table_Structure_Cursor


SET @sql_refmt = LEFT(@sql_refmt, LEN(@sql_refmt) - 5) + CHAR(13) + CHAR(10) + 'FROM ##Tmp_Query_Results'
IF @print_sql = 'Y'
	PRINT REPLACE(@sql_refmt, '##Tmp_Query_Results', '???')
PRINT ''
EXEC(@sql_refmt)


DROP TABLE ##Tmp_Query_Results
DROP TABLE #Tmp_Col_Max_Size