合 SQL Server将查询结果导出为html格式
Tags： MSSQLSQL Serverhtml导出数据查询结果
👉 本文共约224个字，系统预计阅读时间或需1分钟。
代码如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | SELECT N' <html lang="en"><head><title>查询结果</title> <style type="text/css"> body.awr {font:bold 10pt Consolas;color:black;background:White;} table {font:11px Consolas; color:Black; background:#FFFFCC; padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; cellspacing:0px;border:1px solid black;border-collapse:collapse;} th {font:bold 11px Consolas; border:1px solid black;color:White; background:#0066cc; padding:5px; cellspacing:0px;border-collapse:collapse;white-space: nowrap;} td {font-family:Consolas; border:1px solid black;white-space: nowrap; } tr:nth-child(odd) {background:White;} tr:hover {background-color: yellow;} h1.awr {font:bold 20pt Consolas;color:#336699;background-color:White;margin-top:0pt; margin-bottom:0pt;padding:0px 0px 0px 0px;} h2.awr {font:bold 13pt Consolas;color:#336699;background-color:White;margin-top:0pt; margin-bottom:0pt;} th.awrbg {font:bold 10pt Consolas; color:White; background:#0066CC;padding-left:0px; padding-right:0px;padding-bottom:0px} th.awrnc {font:9pt Consolas;color:black;background:White;} th.awrc {font:9pt Consolas;color:black;background:#FFFFCC;} td.awrnc {font:9pt Consolas;color:black;background:White;vertical-align:middle;padding:4;} a.info:hover {background:#eee;color:#000000; position:relative;} a.info span {display: none; } a.info:hover span {font-size:11px!important; color:#000000; display:block;position:absolute;top:30px;left:40px;width:150px;border:1px solid #ff0000; background:#FFFF00; padding:1px 1px;text-align:left;word-wrap: break-word; white-space: pre-wrap;} td.awrc {font:9pt Consolas;color:black;background:#FFFFCC; vertical-align:middle;padding:4;}</style></head> <body class="awr">' as 'results' union ALL SELECT REPLACE((SELECT REPLACE((SELECT CONVERT(NVARCHAR(MAX), (SELECT (SELECT N'★ SQL语句查询结果' FOR XML PATH(''), TYPE) AS 'h3', (SELECT 'avg_logical_reads' as th,'avg_logical_writes' as th,'avg_phys_reads' as th, 'Execution_count' AS th,'stmt_start_offset' AS th,'stmt_end_offset' AS th, 'exec_statement' AS th,'dbid' AS th,'dbname' AS th,'objectid' AS th FOR XML RAW('tr'), ELEMENTS, TYPE) , ( select top 10 (total_logical_reads/execution_count) as td, (total_logical_writes/execution_count) as td, (total_physical_reads/execution_count) as td, Execution_count as td, statement_start_offset as td, statement_end_offset as td, '<textarea style="width:600px;font-family:Consolas;font-size:11px;overflow:auto" rows="3">' + isnull((substring(sql_text.text, (statement_start_offset/2) ,4000)) ,' ')+'</textarea>' as td, plan_text.dbid as td, (select name from sys.sysdatabases b where b.dbid=plan_text.dbid) as td, isnull(plan_text.objectid,' ') as td from sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text(sql_handle) as sql_text cross apply sys.dm_exec_query_plan(plan_handle) as plan_text where sql_text.text not like '%textarea%' order by (total_logical_reads + total_physical_reads) /Execution_count Desc FOR XML RAW('tr'), ELEMENTS, TYPE ) AS 'tbody' FOR XML PATH(''), ROOT('table')))),'<','<')),'>','>'); |
运行
请修改对应部分，例如第23行到25行，第28行到44行，其中th和td不能变。
然后在SSMS中直接执行，将结果拷贝到txt中，另存为html格式即可。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
