html 自动生产,【SQL】用Sql Server自动生产html格式的数据字典

本文软件环境：Sql Server 2008。

1.打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。

直接在表上或者字段上右键属性-扩展属性，添加一个key-value对，key就是“MS_Description",value就是你自己的描述。详细见下图：

给字段添加描述的过程与上述一致，不在赘述。

2.执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。

Set nocount on

DECLARE @TableName nvarchar(35)

DECLARE Tbls CURSOR

FOR

Select distinct Table_name

FROM INFORMATION_SCHEMA.COLUMNS

--put any exclusions here

--where table_name not like '%old'

order by Table_name

OPEN Tbls

PRINT ''

PRINT ''

PRINT '

'

PRINT '

数据库字典'

PRINT '

PRINT 'body{margin:0; font:11pt "arial", "微软雅黑"; cursor:default;}'

PRINT '.tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'

PRINT '.tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#45360A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'

PRINT '.tableBox table {width:1000px; padding:0px }'

PRINT '.tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#F7EBC8; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'

PRINT '.tableBox td {height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; border-right:1px solid #E0C889; border-bottom:1px solid #E0C889 }'

PRINT ''

PRINT ''

PRINT '

'

FETCH NEXT FROM Tbls

INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT '

'

Select '

表' + @TableName + ' : '+cast(Value as varchar(1000)) + '

'

FROM sys.extended_properties A

WHERE A.major_id = OBJECT_ID(@TableName)

and name = 'MS_Description' and minor_id = 0

PRINT '

--Get the Description of the table

--Characters 1-250

PRINT '

' --Set up the Column Headers for the Table

PRINT '

字段名称'

PRINT '

描述'

PRINT '

主键'

PRINT '

外键'

PRINT '

类型'

PRINT '

长度'

PRINT '

数值精度'

PRINT '

小数位数'

PRINT '

允许为空'

PRINT '

计算列'

PRINT '

标识列'

PRINT '

默认值'

--Get the Table Data

SELECT '

',

'

' + CAST(clmns.name AS VARCHAR(35)) + '',

'

' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '',

'

' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '',

'

' + CAST(ISNULL(

(SELECT TOP 1 1

FROM sys.foreign_key_columns AS fkclmn

WHERE fkclmn.parent_column_id = clmns.column_id

AND fkclmn.parent_object_id = clmns.object_id

), 0) AS VARCHAR(20)) + '

',

'

' + CAST(udt.name AS CHAR(15)) + '' ,

'

' + CAST(CAST(CASE WHEN typ.name IN (N'nchar', N'nvarchar') AND clmns.max_length <> -1

THEN clmns.max_length/2

ELSE clmns.max_length END AS INT) AS VARCHAR(20)) + '

',

'

' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '',

'

' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '',

'

' + CAST(clmns.is_nullable AS VARCHAR(20)) + '' ,

'

' + CAST(clmns.is_computed AS VARCHAR(20)) + '' ,

'

' + CAST(clmns.is_identity AS VARCHAR(20)) + '' ,

'

' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + ''

FROM sys.tables AS tbl INNER JOIN sys.all_columns AS clmns

ON clmns.object_id=tbl.object_id

LEFT OUTER JOIN sys.indexes AS idx

ON idx.object_id = clmns.object_id

AND 1 =idx.is_primary_key

LEFT OUTER JOIN sys.index_columns AS idxcol

ON idxcol.index_id = idx.index_id

AND idxcol.column_id = clmns.column_id

AND idxcol.object_id = clmns.object_id

AND 0 = idxcol.is_included_column

LEFT OUTER JOIN sys.types AS udt

ON udt.user_type_id = clmns.user_type_id

LEFT OUTER JOIN sys.types AS typ

ON typ.user_type_id = clmns.system_type_id

AND typ.user_type_id = typ.system_type_id

LEFT JOIN sys.default_constraints AS cnstr

ON cnstr.object_id=clmns.default_object_id

LEFT OUTER JOIN sys.extended_properties exprop

ON exprop.major_id = clmns.object_id

AND exprop.minor_id = clmns.column_id

AND exprop.name = 'MS_Description'

WHERE (tbl.name = @TableName and

exprop.class = 1) --I don't wand to include comments on indexes

ORDER BY clmns.column_id ASC

PRINT '

'

PRINT '

'

FETCH NEXT FROM Tbls

INTO @TableName

END

PRINT ''

CLOSE Tbls

DEALLOCATE Tbls

如下图，第二个框是”以文本方式显示结果”，不要选错了：

3.最终显示效果

后记：

如果你觉得MS_Description这个key写起来太麻烦，当然也可以自定义，不过那段sql代码也要记得改一下。