Column Widths When Attaching Data in xp_sendmail

A colleague asked me for some help with a problem she was having whilst using the extended procedure xp_sendmail. She wanted to use xp_sendmail to mail the results of the system stored procedure sp_helpindex so that certain people could be aware of what indices were present on a table. The problem was that the data coming out had enormous amounts of white space after it no matter how many LTRIMS or RTRIM's you used which meant that it wouldn't present very well in an email. Now, although I use xp_sendmail a fair bit, I rarely use it for executing queries and sending back data. In the end, I came up with a dodgy looking hack that more or less sorted us out for the time being but is distinctly inelegant.

Basically, I created a wrapper SP for sp_helpindex like so:

CREATE PROC dbo.MyProc


AS

    SET NOCOUNT ON


    CREATE TABLE ##idx_temp (
        [index_name] SYSNAME,
        [index_description] VARCHAR(210),
        [index_keys] NVARCHAR(2078)
    )


    INSERT INTO ##idx_temp

    EXEC sp_helpindex 'tablename'

    SELECT CONVERT(VARCHAR(100), index_name), CONVERT(VARCHAR(100), index_description), CONVERT(VARCHAR(100), index_keys)

    FROM ##idx_temp


    DROP TABLE ##idx_temp


    SET NOCOUNT OFF


GO

It's not pretty: is simply limits the column widths to 100 characters (orwhatever your VARCHAR width is set to) but it was an improvement. If anyone knows of a better way of doing this (and I refuse to believe that there isn't) then please, let me know!