Format column length in SSMS output

Question

SQL Server 2012. Sample query at the bottom of this post.

I'm trying to create a simple report for when a given database was last backed up.

When executing the sample query with output to text in SSMS, the DB_NAME column is formatted to be the max possible size for data (same issue exists in DB2, btw). So, I've got a column that contains data that is never more than, say, 12 characters, but it's stored in a varchar(128), I get 128 characters of data no matter what. RTRIM has no effect on the output.

Is there an elegant way that you know of to make the formatted column length be the max size of actual data there, rather than the max potential size of data?

I guess there exists an xp_sprintf() function, but I'm not familiar with it, and it doesn't look terribly robust.

I've tried casting it like this:

DECLARE @Servername_Length int;
SELECT  @Servername_Length =        LEN(    CAST(   SERVERPROPERTY('Servername') AS VARCHAR(MAX)    )   ) ;

...
SELECT  
   CONVERT(CHAR(@Servername_Length), SERVERPROPERTY('Servername')) AS Server, 
...

But then SQL Server won't let me use the variable @database_name_Length in my varchar definition when casting. SQL Server, apparently, demands a literal number when declaring the char or varchar variable.

I'm down to building the statement in a string and using something like sp_executesql, or building a temp table with the actual column lengths I need, both of which are really a bit more trouble than I was hoping to go to just to NOT get 100 spaces in my output on a 128 character column.

Have searched the interwebs and found bupkus.

Maybe I'm searching for the wrong thing, or Google is cross with me.

It seems that SSMS will format the column to be the maximum size allowed, even if the actual data is much smaller. I was hoping for an elegant way to "fix" this without jumping through hoops. I'm using SSMS 2012.

If I go to Results To Grid and then to Excel or something similar, the trailing space is eliminated. I was hoping to basically create a report that I email, though.

Sample query

--------------------------------------------------------------------------
QUERY:
--------------------------------------------------------------------------
SELECT  
   CONVERT(CHAR(32), SERVERPROPERTY('Servername')) AS Server, 
   '''' + msdb.dbo.backupset.database_name + '''',  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name  
ORDER BY  
   msdb.dbo.backupset.database_name 

Answer 1

You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in text output view.

Convert your query into something like:

SELECT [Server] = CONVERT(VARCHAR(30), SERVERPROPERTY('Servername')) 
   , DatabaseName = CONVERT(VARCHAR(30), '''' + bs.database_name + '''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(30), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id  
WHERE  bs.[type] = 'D' 
GROUP BY bs.database_name  
ORDER BY bs.database_name;

This will give output similar to:

Server                         DatabaseName                   LastDatabaseBackupDate
------------------------------ ------------------------------ ------------------------------
[ServerName]                   'A'                            Sep 25 2015 11:32AM
[ServerName]                   'B'                            Apr 21 2015 12:09PM
[ServerName]                   'C'                            Feb 24 2015  9:16PM
[ServerName]                   'D'                            Oct  8 2014 11:02AM
[ServerName]                   'E'                            May 14 2014  6:27PM

(5 row(s) affected)

If you want to be able to dynamically change the column widths without modifying the T-SQL code, you'll need to use Dynamic SQL:

DECLARE @ColumnWidth VARCHAR(4);
DECLARE @Cmd NVARCHAR(MAX);

SET @ColumnWidth = '24';
SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidth + '), SERVERPROPERTY(''Servername'')) 
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidth + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidth + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id  
WHERE  bs.[type] = ''D'' 
GROUP BY bs.database_name  
ORDER BY bs.database_name;
';

EXEC (@cmd);

Here, I've set the width to 24 for all columns, and it comes out looking like:

Server                   DatabaseName             LastDatabaseBackupDate
------------------------ ------------------------ ------------------------
SERVERNAME               'A'                      Sep 25 2015 11:32AM
SERVERNAME               'A'                      Apr 21 2015 12:09PM
SERVERNAME               'A'                      Feb 24 2015  9:16PM
SERVERNAME               'A'                      Oct  8 2014 11:02AM
SERVERNAME               'A'                      May 14 2014  6:27PM

(5 row(s) affected)

If you really want to go crazy and have the columns automatically size themselves, you would do this:

DECLARE @ColumnWidthServer VARCHAR(4);
DECLARE @ColumnWidthDatabase VARCHAR(4);
DECLARE @ColumnWidthLastBackup VARCHAR(4);

DECLARE @Cmd NVARCHAR(MAX);

SELECT @ColumnWidthServer = 1 + LEN(CONVERT(VARCHAR(128), SERVERPROPERTY('Servername')))
   , @ColumnWidthDatabase = 1 + MAX(LEN('''' + bs.database_name + ''''))
   , @ColumnWidthLastBackup = 1 + MAX(LEN(CONVERT(VARCHAR(128), bs.backup_finish_date)))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id  
WHERE  bs.[type] = 'D';

SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidthServer + '), SERVERPROPERTY(''Servername'')) 
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidthDatabase + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidthLastBackup + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id  
WHERE  bs.[type] = ''D'' 
GROUP BY bs.database_name  
ORDER BY bs.database_name;
';

EXEC (@cmd);

Answer 2

If you want something quick and easy, and can accept all column widths being the same, then try the -Y option of SQLCMD.exe:

C:\>SQLCMD -Y 3 -Q "SELECT name, name, name from sys.objects;"
nam nam nam
--- --- ---
sys sys sys
pla pla pla
spt spt spt
fai fai fai
MSr MSr MSr
sp_ sp_ sp_

 
OR, since the goal here is to email the output as a report, you can use sp_send_dbmail as follows:

DECLARE @ReportQuery NVARCHAR(MAX) = N'SET NOCOUNT ON;

PRINT ''<table style="border:1px solid black; width:100%;">
<tr><th>Server</th><th>DatabaseName</th><th>LastBackupDate</th></tr>'';

SELECT CONCAT(
       ''<tr><td>'',
       CONVERT(sysname, SERVERPROPERTY(''Servername'')),
       ''</td><td>'',
       N'''''''' + bset.[database_name] + N'''''''',
       ''</td><td>'',
       MAX(bset.backup_finish_date),
       ''</td></tr>'')
FROM   msdb.dbo.backupmediafamily bfam
INNER JOIN msdb.dbo.backupset bset
       ON bset.media_set_id = bfam.media_set_id
WHERE  bset.[type] = ''D''
GROUP BY      bset.[database_name]
ORDER BY      bset.[database_name];

PRINT ''</table>'';
';

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = N'{your_Profile_name}',
  @recipients = N'{email_address(es)}',
--  @copy_recipients = N'copy_recipient [ ; ...n ]',
--  @blind_copy_recipients = N'blind_copy_recipient [ ; ...n ]',
  @subject = N'Reporty Stuffs', -- NVARCHAR(255)
  @body = N'Here is the report you asked for...',
  @body_format = 'html', -- HTML or TEXT (default)
--  @importance = 'importance', -- Low, Normal (default), or High
--  @sensitivity = 'sensitivity', -- Normal (default), Personal, Private, Confidential
  @query = @ReportQuery,
--  @execute_query_database = N'DB name',
  @query_result_header = 0; -- 0 or 1 (default)

Notes:

1. In the Report Query (@ReportQuery in the example):
   1. SET NOCOUNT ON; is required, else "X row(s) affected" prints between the final </td></tr> and the </table> tag, messing up the rendering of the table
   2. CONCAT makes it easy to construct the HTML table row because you can skip doing CONVERT(NVARCHAR(x), ... for non-string columns of types that can implicitly convert. For pre-SQL Server 2012 (when CONCAT was introduced), just do the CONVERTs and normal string concatenation with +.
2. Query output will come after the content of the @body variable (if there is any).
3. @body_format = 'html' is required, else the < and > of the HTML tags will get translated into < and >, respectively, and you will see the HTML tags (since they won't actually be HTML tags).
4. @query_result_header = 0 is required, else a column header line prints between the <table> tag and the initial <tr><td>, messing up the table rendering.

Answer 3

I have written a function to get specific length format output. Here's my solution. You can use the same in your SQL query:

CREATE Function [dbo].[UFN_COLUMNFORMAT]        
(@format nVARCHAR(10), @value nVARCHAR(100))      
Returns nVarchar(max)      
 As      
 Begin      
 DECLARE  @value1 VARCHAR(100), @Fchar VARCHAR(5), @innerchar VARCHAR(5), @symbol VARCHAR(1),        
 @leninnerchar INT, @FFormat CHAR(20), @m INT, @n INT, @sql NVARCHAR(max),@ValueFordate datetime      

 SELECT @Fchar = Substring(@format, 1, Charindex('(', @format) - 1)      

 SELECT @innerchar = Substring(@format, Charindex('(', @format) + 1, ((Charindex(')', @format) - Charindex('(', @format)) - 1))      

 SELECT @symbol = Substring(@format, Charindex(')', @format) + 1, len(@format))      


 SELECT @m = CASE        
   WHEN charindex('#', @innerchar) > 0      
    THEN substring(@innerchar, 1, charindex('#', @innerchar) - 1)      
   WHEN charindex(',', @innerchar) > 0      
    THEN substring(@innerchar, 1, charindex(',', @innerchar) - 1)      
   ELSE 0      
   END      

 SELECT @n = CASE        
   WHEN charindex('#', @innerchar) > 0      
    THEN substring(@innerchar, charindex('#', @innerchar) + 1, len(@innerchar))      
   WHEN charindex(',', @innerchar) > 0      
    THEN substring(@innerchar, charindex(',', @innerchar) + 1, len(@innerchar))      
   ELSE @innerchar      
   END      



 SELECT @FFormat = CASE        
   WHEN @Fchar = 'A'      
    THEN 'A'      
   WHEN @Fchar = 'A0'      
    THEN 'A0'      
   WHEN @Fchar = 'N'      
    THEN 'N'      
   WHEN @Fchar = 'Date'      
    THEN 'Date'      
   END + '(' + CASE        
   WHEN charindex('#', @innerchar) > 0      
    THEN 'm#n'      
   WHEN charindex(',', @innerchar) > 0      
    THEN 'm,n'      
   ELSE 'n'      
   END + ')' + CASE        
   WHEN @symbol = ''      
    THEN ''      
   ELSE @symbol      
   END      


   IF @FFormat='A(n)'      

   Select @value1=  LEFT(CAST( @value   AS VARCHAR( max)) + REPLICATE(' ', @n) ,@n)      
   IF @FFormat='A(n)+'      

   Select @value1=   LEFT(@symbol+CAST( @value   AS VARCHAR( max) )+ REPLICATE(' ', @n),@n)      
   IF @FFormat='A0(n)'      

   Select @value1=   LEFT(CAST( @value   AS VARCHAR( max)) +REPLICATE('0', @n),@n)      
   IF @FFormat='A0(n)+'      

   Select @value1=    @symbol+LEFT( REPLICATE('0', @n-1) +CAST( @value   AS VARCHAR( max) ),@n-1)      
   IF @FFormat='A(m#n)'      

   Select @value1=    LEFT(CAST( replace(@value,'.','')   AS VARCHAR( max) )+ REPLICATE(' ',@m),@m)      
    IF @FFormat='A0(m#n)'      

   Select @value1=     LEFT( CAST( replace(@value,'.','')   AS VARCHAR( max) )+REPLICATE('0',@m),@m)      
    IF @FFormat='A0(m,n)+'      


   Select @value1=     @symbol+  LEFT( REPLICATE('0',@m-@n-1) +CAST( substring(@value,1,CHARINDEX('.',@value))   AS VARCHAR( max) ),@m-@n-1)+ Left(  CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value))   AS VARCHAR( max) )+REPLICATE('0', @n) , @n)

   IF @FFormat='A0(m,n)'      


   Select @value1=     LEFT( CAST( substring(@value,1,CHARINDEX('.',@value))   AS VARCHAR( max) ) + REPLICATE('0',@m-@n) ,@m-@n)+ Left(  CAST( substring(@value,CHARINDEX('.',@value)+1,len(@value))   AS VARCHAR( max) )+REPLICATE('0', @n) , @n)      
   IF @FFormat='N(m,n)'      

   Select @value1=      LEFT(CAST( @value   AS VARCHAR( max) )+REPLICATE('0',@m-1),@m-1)      
    IF @FFormat='N(n)'      

   Select @value1=      LEFT(CAST( @value   AS VARCHAR( max) )+REPLICATE(' ',@n),@n)      
   IF @FFormat='Date(n)'      
   select @value1=  convert(varchar(50),convert(datetime,@value),112)
   IF @FFormat='Date(n)'      
   select @value1=  convert(varchar(50),convert(datetime,@value),20)          
   Return @value1      
 End