作者：邹建
时间：2008年 11月 10日
IF OBJECT_ID(N'dbo.p_ExportHtml') IS NOT NULL
DROP PROC dbo.p_ExportHtml;
GO
/*-- == 导出表/视图中的数据为html 文件======================
此存储过程用于将指定的表/视图中的数据导出为 html 文件
由于是使用存储过程, 因此文件目录基于 sql server 服务器
存储过程中会使用xp_cmdshell 调用bcp 来写文件
因此必须打开xp_cmdshell 功能, 可以使用下面的脚本实现
EXEC sp_configure N'show advanced options', 1 RECONFIGURE;
EXEC sp_configure N'xp_cmdshell', 1 RECONFIGURE;
---------------------------------------------------------
-- 调用示例
EXEC dbo.p_ExportHtml
@object_name = N'sys.objects',
@file_name = N'c:\test.html';
---------------------------------------------------------
-- 环境要求
适用于sql server 2005 或者更高的版本
-- ==== 邹建2008.11(引用请保留此信息) =============== */
CREATE PROC dbo.p_ExportHtml
@object_name sysname,
@file_name nvarchar(260),
@title nvarchar(1000) = NULL -- html 标题, 为NULL时使用@object_name
AS
SET NOCOUNT ON;
DECLARE
@sql_field nvarchar(max),
@sql_body nvarchar(max);
SELECT
@sql_field = N'',
@sql_body = N''
SELECT
@sql_field = @sql_field
+ N' UNION ALL SELECT field_name = N' + QUOTENAME(C.name, N''''),
@sql_body = @sql_body
+ N', [td/@align] = '
+ QUOTENAME(
CASE
WHEN T.name LIKE N'%int'
OR T.name LIKE N'%money'
OR T.name IN(N'real', N'float', N'decimal', N'numeric')
THEN 'right'
WHEN T.name IN(N'bit')
THEN 'center'
ELSE 'left'
END,
'''')
+ N', [td] = '
+ CASE
WHEN T.name IN(N'xml')
THEN N'CONVERT(nvarchar(max), ' + QUOTENAME(C.name) + N')'
ELSE QUOTENAME(C.name)
END
+ N', NULL'
FROM sys.all_columns C
INNER JOIN sys.types T
ON T.system_type_id = C.system_type_id
AND T.system_type_id = T.user_type_id
WHERE C.object_id = OBJECT_ID(@object_name)
ORDER BY C.column_id;
IF @@ROWCOUNT = 0
BEGIN
RAISERROR(N'can''t found object "%s"', 16, 1, @object_name);
RETURN -1;
END
DECLARE
@temp_proce_name sysname,
@sql nvarchar(max),
@cmd nvarchar(4000);
SELECT
@temp_proce_name = QUOTENAME(N'##_'
+ CONVERT(char(10), GETDATE(), 112)
+ N'_' + CONVERT(char(36), NEWID())
),
@sql = N'
CREATE PROC ' + @temp_proce_name + N'
AS
WITH
FIELD AS(
' + STUFF(@sql_field, 1, 11, N'') + N'
),
DATA AS(
SELECT * FROM ' + @object_name + N'
)
SELECT
[H1] = N''' + REPLACE(ISNULL(@title, @object_name), N'''', N'''''') + N''',
[table/@border] = 1,
[tr] = (
SELECT
[*] = field_name
FROM FIELD
FOR XML PATH(''th''), TYPE
),
[*] = (
SELECT ' + STUFF(@sql_body, 1, 2, N'') + N'
FROM DATA
FOR XML PATH(''tr''), TYPE
)
FOR XML PATH(''html''), TYPE;
';
-- 生成临时处理存储过程
EXEC sp_executesql
@sql;
-- 输出结果到文件
SET @cmd = N'bcp "EXEC ' + QUOTENAME(DB_NAME())
+ N'.' + @temp_proce_name
+ N'" queryout ' + QUOTENAME(@file_name, N'"')
+ N' /T /w';
DECLARE @tb_re TABLE(
col nvarchar(4000)
);
INSERT @tb_re
EXEC sys.xp_cmdshell @cmd;
DECLARE
@file_exist int;
EXEC master.sys.xp_fileexist @file_name, @file_exist OUTPUT;
-- 删除临时存储过程
IF OBJECT_ID(@temp_proce_name) IS NOT NULL
EXEC(N'DROP PROC ' + @temp_proce_name + N';');
-- 检查文件生成是否成功
IF @file_exist = 1
BEGIN
RAISERROR(N'object "%s" output to "%s" succeed.', 10, 1, @object_name, @file_name);
END
ELSE
BEGIN
SELECT * FROM @tb_re;
RAISERROR(N'some error occur when generate html file, please check output information', 16, 1);
RETURN -1;
END
GO