SQL MAIL 输出表格疑问

Question

这个要设置CSS的
找个懂CSS的人帮你把代码写好
你套格式去拼吧

Answer 1

最近搞报告给上司，这是可用的，不过只在2008R2以上试过，格式没问题，你自己调吧
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @date SMALLDATETIME
SET @date = CONVERT(CHAR(10), GETDATE(), 120)
DECLARE @title VARCHAR(64)
SELECT  @title = CONVERT(CHAR(10), GETDATE(), 120) + ' DB Check Report '
DECLARE @DBA VARCHAR(1024)
SET @DBA ='[email protected]'SET @tableHTML = N'<H1> ServerInformations</H1>' + N'<table border="1">'
    + N'<tr><th>ServerName</th><th>HostName</th>'
    + N'<th>Edtion</th><th>InstanceName</th><th>ProductVersion</th>'
    + N'<th>ProductLevel</th><th>CPU_Counts</th><th>Physical_Memory(GB)</th>' 
+ N'<th>SQLServer_Start_Time</th><th>Virtual_Machine</th><th>CaptureDate</th>' 
  + CAST(( SELECT td = ServerName ,
                    '' ,
                    td = HostName,
                    '' ,
                    td = Edition,
                    '' ,
                    td = InstanceName ,
                    '' ,
                    td=ProductVersion ,
                    '' ,
                    td = ProductLevel,''
 ,
                    td = CPU_Counts ,
                    '' ,
                    td=[Physical_Memory(GB)] ,
                    '' ,
                    td = SQLServer_Start_Time,''
 ,
                    '' ,
                    td=Virtual_Machine ,
                    '' ,
                    td = CaptureDate,''
                  
             from AuditDB.dbo.ServerInformations
where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'SET @tableHTML =@tableHTML+   N'<H1> DBLists</H1>' + N'<table border="1">'
    + N'<tr><th>name</th><th>State_Desc</th>'
    + N'<th>Create_Date</th><th>compatibility_level</th><th>collation_name</th>'
    + N'<th>is_auto_create_stats_on</th><th>is_auto_update_stats_on</th><th>recovery_model_desc</th>' 
+ N'<th>is_published</th><th>is_distributor</th><th>log_reuse_wait_desc</th>' 
+ N'<th>mirroring_state_desc</th><th>mirroring_partner_name</th><th>mirroring_role_desc</th>'  + N'<th>mirroring_safety_level_desc</th><th>CaptureDate</th>'   + CAST(( SELECT td = name ,
                    '' ,
                    td = State_Desc,
                    '' ,
                    td = Create_Date,
                    '' ,
                    td = compatibility_level ,
                    '' ,
                    td=collation_name ,
                    '' ,
                    td = is_auto_create_stats_on,''
 ,
                    td = is_auto_update_stats_on ,
                    '' ,
                    td=recovery_model_desc ,
                    '' ,
                    td = is_published,''
 ,
                    '' ,
                    td=is_distributor ,
'' ,
                    td=log_reuse_wait_desc ,
                    '' ,
                    td = mirroring_state_desc,''
 ,
 '' ,
                    td=mirroring_role_desc ,
                    '' ,
                    td = mirroring_safety_level_desc,''
 ,                  
                    '' ,
                    td = CaptureDate,''
                  
             from AuditDB.dbo.DBLists
where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'SET @tableHTML =@tableHTML+   N'<H1> DBFiles</H1>' + N'<table border="1">'
    + N'<tr><th>DBName</th><th>FILENAME</th>'
    + N'<th>physical_name</th><th>type_desc</th><th>currentsize</th>'
    + N'<th>LimitSIZE(MB)</th><th>GrowTH</th><th>CaptureDate</th>' 
  + CAST(( SELECT td = DBName ,
                    '' ,
                    td = [FILENAME],
                    '' ,
                    td = physical_name,
                    '' ,
                    td = type_desc ,
                    '' ,
                    td=currentsize ,
                    '' ,
                    td = [LimitSIZE(MB)],''
 ,
                    td = GrowTH ,
                    '' ,
                    td=CaptureDate ,
                    ''
                  
             from AuditDB.dbo.DBFiles
where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'
           
SET @tableHTML =@tableHTML+   N'<H1> LongRunningQuerys</H1>' + N'<table border="1">'
    + N'<tr><th>total_worker_time</th><th>execution_count</th>'
    + N'<th>total_physical_reads</th><th>total_logical_reads</th><th>total_logical_writes</th>'
    + N'<th>last_rows</th><th>Avg CPU Time</th><th>queryText</th>' 
 + N'<th>CaptureDate</th>' 
  + CAST(( SELECT td = total_worker_time ,
                    '' ,
                    td = execution_count,
                    '' ,
                    td = total_physical_reads,
                    '' ,
                    td = total_logical_reads ,
                    '' ,
                    td=total_logical_writes ,
                    '',
                    td=last_rows,
                    '' ,
                    td =[Avg CPU Time],''
 ,
                    td = queryText ,
                    '' ,
                    td=CaptureDate ,
                    ''
             from AuditDB.dbo.LongRunningQuery
where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'
 
SET @tableHTML =@tableHTML+   N'<H1> Top 5 Waits</H1>' + N'<table border="1">'
    + N'<tr><th>Wait_type</th><th>Waiting_tasks_count</th>'
    + N'<th>Wait_time_ms</th><th>Max_wait_time_ms</th><th>Signal_wait_time_ms</th>'
 + N'<th>CaptureDate</th>' 
  + CAST(( SELECT top 5 td = Wait_type ,
                    '' ,
                    td = Waiting_tasks_count,
                    '' ,
                    td = Wait_time_ms,
                    '' ,
                    td = Max_wait_time_ms ,
                    '' ,
                    td=Signal_wait_time_ms ,
                    '' ,
                    td=getdate(),
                    ''
             from AuditDB.dbo.WaitStats
             where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()
               order by Wait_time_ms desc
           FOR
             XML PATH('tr') ,
                 TYPE
                
           ) AS NVARCHAR(MAX)) + N'</table>'SET @tableHTML =@tableHTML+   N'<H1>Unused Indexes</H1>' + N'<table border="1">'
    + N'<tr><th>Statement</th><th>index_name</th>'
    + N'<th>user_reads</th><th>user_writes</th><th>total_rows</th>'
 + N'<th>CaptureDate</th>' 
  + CAST(( SELECT td = [Statement] ,
                    '' ,
                    td = index_name,
                    '' ,
                    td = user_reads,
                    '' ,
                    td = user_writes ,
                    '' ,
                    td=total_rows ,
                    '' ,
                    td=CaptureDate ,
                    ''
             from (SELECT  '[' + DB_NAME() + '].[' + su.[name] + '].[' + o.[name] + ']' AS [Statement] ,
        i.[name] AS [index_name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] AS [user_reads] ,
        ddius.[user_updates] AS [user_writes] ,
        SUM(SP.rows) AS [total_rows],GETDATE() AS CaptureDate
FROM    sys.dm_db_index_usage_stats ddius
        INNER JOIN sys.indexes i ON ddius.[object_id] = i.[object_id]
                                    AND i.[index_id] = ddius.[index_id]
        INNER JOIN sys.partitions SP ON ddius.[object_id] = SP.[object_id]
                                        AND SP.[index_id] = ddius.[index_id]
        INNER JOIN sys.objects o ON ddius.[object_id] = o.[object_id]
        INNER JOIN sys.sysusers su ON o.[schema_id] = su.[UID]
WHERE   ddius.[database_id] = DB_ID() -- current database only 
        AND OBJECTPROPERTY(ddius.[object_id], 'IsUserTable') = 1
        AND ddius.[index_id] > 0
GROUP BY su.[name] ,
        o.[name] ,
        i.[name] ,
        ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] ,
        ddius.[user_updates]
HAVING  ddius.[user_seeks] + ddius.[user_scans] + ddius.[user_lookups] = 0
)a
where CaptureDate between dateadd(dd,-1,GETDATE()) and GETDATE()           FOR
             XML PATH('tr') ,
                 TYPE
           ) AS NVARCHAR(MAX)) + N'</table>'EXEC msdb.dbo.sp_send_dbmail @profile_name='DBAEmail',@recipients = @DBA, @subject = @title,
    @body = @tableHTML, @body_format = 'HTML' ;
--select * from msdb.dbo.sysmail_profile

Answer 2

<table border = "0" style="table-layout:fixed ">  修改一下开头看看

Answer 3

看反了，你是要有border的哈，试试这个<style>  
      .tt th{background:#a9a9a9;background-color:red;color:white;}
      .tt tr{font-weight:100;font-size:12;width:120pt;text-align:center;font-family:Lucida Sans Unicode;}
      .tt tr.altrow{
background-color:#c7e5ff;
      }
      .tt td{width:100px;padding:5px;word-wrap: break-word; word-break : break-all;}
     </style>'
<table class = "tt" border="1" style="border-collapse:collapse;table-layout:fixed;word-break:break-all;">