【分享】生成html格式的数据库字典
当开发用到数据模型的程序时，数据字典可以帮助你理解数据项适合结构中的哪个地方，它可能包含什么数值，以及数据项基本上表示现实世界中的什么意思。例如，一家银行或者是一个银行组织可能对客户银行业涉及的数据对象进行建模。他们需要给银行程序员提供数据字典。这个数据字典就描述了客户银行业中的数据模型每一个数据项（例如，“账户持有人”和“可用信用”）。
下面就来列举一下如何生成html格式的数据库字典
1.打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。
直接在表上或者字段上右键属性-扩展属性，添加一个key-value对，key就是“MS_Description",value就是你自己的描述。详细见下图：
2.执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。
Set nocount onDECLARE TableName nvarchar(35),TableComment nvarchar(200)DECLARE Tbls CURSORFORSelect distinct b.TABLE_NAME,convert(nvarchar(200),isnull(g.[value],'-'))AS TABLE_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS b
inner join sys.tables a on a.name=b.TABLE_NAME
left join sys.extended_properties g on(a.object_id=g.major_id AND g.minor_id=0)
--put any exclusions here
--where table_name not like'%old'
where b.table_name like'%dbo_%'------此处是要生成数据字典的表的范围
order by b.Table_nameOPEN TblsPRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'PRINT '<html xmlns="http://www.w3.org/1999/xhtml">'PRINT '<head>'PRINT '<title>数据库字典</title>'PRINT '<meta http-equiv="content-type" content="text/html;charset=utf-8">'PRINT '<style type="text/css">'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 '</style>'PRINT '</head>'PRINT '<body>'FETCH NEXT FROM TblsINTO @TableName,@TableCommentWHILE @@FETCH_STATUS = 0BEGIN
PRINT'<div class="tableBox">'
PRINT'<h3>'+TableName+''+TableComment+'</h3>'
PRINT'<table cellspacing="0">'
--Get the Description of the table
--Characters 1-250
PRINT'<tr>'--Set up the Column Headers for the Table
PRINT'<th>字段名称</th>'
PRINT'<th>描述</th>'
PRINT'<th>主键</th>'
PRINT'<th>外键</th>'
PRINT'<th>类型</th>'
PRINT'<th>长度</th>'
PRINT'<th>数值精度</th>'
PRINT'<th>小数位数</th>'
PRINT'<th>允许为空</th>'
PRINT'<th>计算列</th>'
PRINT'<th>标识列</th>'
PRINT'<th>默认值</th>'
--Get the Table Data
SELECT'</tr><tr>',
'<td>'+CAST(clmns.name AS VARCHAR(35))+'</td>',
'<td>'+ISNULL(CAST(exprop.value AS VARCHAR(500)),'')+'</td>',
'<td>'+CAST(ISNULL(idxcol.index_column_id,0)AS VARCHAR(20))+'</td>',
'<td>'+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))+'</td>',
'<td>'+CAST(udt.name AS CHAR(15))+'</td>',
'<td>'+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))+'</td>',
'<td>'+CAST(CAST(clmns.precision AS INT)AS VARCHAR(20))+'</td>',
'<td>'+CAST(CAST(clmns.scale AS INT)AS VARCHAR(20))+'</td>',
'<td>'+CAST(clmns.is_nullable AS VARCHAR(20))+'</td>',
'<td>'+CAST(clmns.is_computed AS VARCHAR(20))+'</td>',
'<td>'+CAST(clmns.is_identity AS VARCHAR(20))+'</td>',
'<td>'+isnull(CAST(cnstr.definition AS VARCHAR(20)),'')+'</td>'
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'</tr></table>'
PRINT'</div>'
FETCH NEXT FROM Tbls
INTO TableName,TableCommentENDPRINT'</body></HTML>'CLOSE TblsDEALLOCATE Tbls
如下图，第二个框是”以文本方式显示结果”，不要选错了：
如果你觉得MS_Description这个key写起来太麻烦，当然也可以自定义，不过那段sql代码也要记得改一下。
关注我们，查看更多品牌设计、网站开发、定制软件开发、小程序开发、SEO推广知识！！！
