主页 / dba / 问题 / 117484
Accepted
Joe Hayes
Asked: 2015-10-09 13:48:01 +0800 CST

格式化 SSMS 输出中的列长度

  • 772

SQL Server 2012。本文底部的示例查询。

我正在尝试为上次备份给定数据库的时间创建一个简单的报告。

在 SSMS 中执行带有文本输出的示例查询时，该DB_NAME列被格式化为数据的最大可能大小（DB2 中存在同样的问题，顺便说一句）。因此，我有一列包含的数据不超过 12 个字符，但它存储在 avarchar(128)中，无论如何我都会得到 128 个字符的数据。RTRIM对输出没有影响。

您是否知道一种优雅的方法可以使格式化的列长度成为实际数据的最大大小，而不是数据的最大潜在大小？

我想存在一个xp_sprintf()函数，但我不熟悉它，而且它看起来不是非常健壮。

我试过这样投射它：

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

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

但是 SQL Server 不允许我在转换时使用定义中的@database_name_Length变量varchar。显然，SQL Server 在声明charorvarchar变量时需要一个文字数字。

我决定在一个字符串中构建语句并使用类似的东西sp_executesql，或者用我需要的实际列长度构建一个临时表，这两者都比我希望去的麻烦多一点，只是为了不得到 100我的输出中的空格在 128 个字符的列上。

搜索了互联网并找到了bupkus。

也许我在寻找错误的东西，或者谷歌对我有意见。

似乎 SSMS 会将列格式化为允许的最大大小，即使实际数据要小得多。我希望有一种优雅的方式来“解决”这个问题，而无需跳过箍。我正在使用 SSMS 2012。

如果我转到结果到网格，然后转到 Excel 或类似的东西，尾随空格将被消除。不过，我希望基本上可以创建一份我通过电子邮件发送的报告。

示例查询

--------------------------------------------------------------------------
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
sql-server sql-server-2012

3 个回答

  1. Best Answer

    CONVERT(VARCHAR(xx), ColumnName)如果您希望列在文本输出视图中显得更短，则需要在所有列上使用。

    将您的查询转换为：

    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;

    这将提供类似于以下内容的输出：

    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)

    如果您希望能够在不修改 T-SQL 代码的情况下动态更改列宽，则需要使用动态 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);

    在这里，我将所有列的宽度设置为 24，它看起来像：

    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)

    如果您真的想发疯并让列自动调整大小，您可以这样做：

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

  2. 如果您想要快速简单的东西，并且可以接受所有列宽相同，请尝试SQLCMD.exe-Y选项：

    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_

     
    或者，由于这里的目标是将输出作为报告通过电子邮件发送，因此您可以使用sp_send_dbmail，如下所示：

    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)

    笔记：

    1. 在报告查询中（@ReportQuery在示例中）：
      1. SET NOCOUNT ON;是必需的，否则“受影响的 X 行”会在最终</td></tr>和</table>标签之间打印，从而打乱表格的呈现
      2. CONCAT使构建 HTML 表格行变得容易，因为您可以跳过CONVERT(NVARCHAR(x), ...对可以隐式转换的类型的非字符串列的操作。对于 SQL Server 2012 之前的版本（何时CONCAT引入），只需将CONVERTs 和普通字符串连接到+.
    2. 查询输出将出现在@body变量的内容之后（如果有的话）。
    3. @body_format = 'html'是必需的，否则 HTML 标记的<和>将分别翻译为&lt;和&gt;，您将看到 HTML 标记（因为它们实际上不是HTML 标记）。
    4. @query_result_header = 0<table>是必需的，否则标签和初始之间会打印列标题行<tr><td>，从而弄乱表格渲染。
    • 5

  3. 我编写了一个函数来获取特定长度的格式输出。这是我的解决方案。您可以在 SQL 查询中使用相同的：

    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
    • 0

相关问题