Weekly Update #004

I use sp_send_dbmail to send results of sql queries by email to business users. Recently an issue was raised that data was being cut off after 255 characters. To fix this I added @query_no_truncate = 1, however this stopped the column headings from being included. No idea why you can't have all the data and column headings but there you have it.

What I am doing now is running 2 queries, one to get the headings, and one to get the data. In theory you should be able to combine them with a Union however you then have datatype issues for non text columns so I gave up with that idea.

My results have 60 something columns (don't ask its for a data import into a third party system!) so I am not typing them all out. I can shove query results into a temporary table and then execute to get a list of columns.

SELECT name 
FROM tempdb.sys.columns 
WHERE object_id = object_id('tempdb..#TempTable')

However I need my list to be horizontal so I can use as column headers. I can use dynamic SQL and a pivot to flip them round.

DECLARE @cols AS NVARCHAR(MAX), @query  AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(name) 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id('tempdb..#TempTable') 
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = N'SELECT ' + @cols + N' FROM 
(
    SELECT name 
    FROM tempdb.sys.columns 
    WHERE object_id = object_id(''tempdb..#TempTable'')
) x
PIVOT 
(
    MAX(name)
    FOR name IN (' + @cols + N')
) y'