Format column length in SSMS output

1.2K Asked by ankur_3579 in SQL Server , Asked on Apr 16, 2021

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 (the 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 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

Answered by Angela Baker

You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in the 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 &#40;@cmd&#41;;

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);

Your Answer

Answer (1)

Ranjana

To format column lengths in SQL Server Management Studio (SSMS) output, you can use several techniques depending on the specific requirements and the type of output you are working with. Here are a few common approaches:

1. Using FORMAT Function

The FORMAT function allows you to format the appearance of date, number, and currency data in your query results.

SELECT 

    FORMAT(someNumberColumn, 'N2') AS FormattedNumber, -- Format number with 2 decimal places

    FORMAT(someDateColumn, 'yyyy-MM-dd') AS FormattedDate -- Format date as YYYY-MM-DD

FROM 

    YourTable;

Example:

2. Using CAST and CONVERT

You can also use CAST and CONVERT functions to change the data type and format the output accordingly.

Example:

  SELECT     CAST(someNumberColumn AS DECIMAL(10,2)) AS FormattedNumber,    CONVERT(VARCHAR(10), someDateColumn, 120) AS FormattedDate -- Format date as YYYY-MM-DDFROM     YourTable;3. Using LEFT, RIGHT, and SUBSTRING

For text columns, you might want to limit the length of the output using LEFT, RIGHT, and SUBSTRING functions.

Example:

  SELECT     LEFT(someTextColumn, 10) AS ShortText -- Limit text to 10 charactersFROM     YourTable;

4. Setting Column Width in SSMS Results to Text

If you are exporting results or want to control column width in the "Results to Text" mode in SSMS, you can adjust the settings.

  Go to Tools > Options.

In the Options dialog, expand Query Results > SQL Server > Results to Text.

Set the Maximum number of characters displayed in each column to your desired value.

5. Using FORMATMESSAGE for Custom Formatting

FORMATMESSAGE can be useful for custom formatting strings.

Example:

  SELECT     FORMATMESSAGE('%-10s', someTextColumn) AS PaddedText -- Pad text to 10 characters widthFROM     YourTable;

6. Using Space Padding for Fixed Column Width

You can manually pad strings with spaces to achieve a fixed column width.

Example:

  SELECT     LEFT(someTextColumn + REPLICATE(' ', 10), 10) AS FixedWidthTextFROM     YourTable;

Example Query Combining Techniques

Here’s a comprehensive example combining several formatting techniques:

  SELECT     FORMAT(someNumberColumn, 'N2') AS FormattedNumber,    FORMAT(someDateColumn, 'yyyy-MM-dd') AS FormattedDate,    LEFT(someTextColumn, 10) AS ShortText,    FORMATMESSAGE('%-20s', someOtherTextColumn) AS PaddedText,    LEFT(someTextColumn + REPLICATE(' ', 10), 10) AS FixedWidthTextFROM     YourTable;

This query formats numeric and date columns, shortens a text column to a fixed length, pads another text column to a fixed width, and combines several methods to achieve well-formatted output.

Conclusion

By using functions like FORMAT, CAST, CONVERT, and FORMATMESSAGE, along with SSMS settings, you can control the format and appearance of your query results to suit your needs. Adjusting these techniques will help you achieve the desired output formatting in SSMS.

4 Months