实用技巧：利用SQL Server的扩展属性自动生成数据字典

xuke123

　　可能是我太落伍了，今天才知道SQL2005的扩展属性还可以这么用。
　　数据字典的重要性就不用多说了，再小的开发团队，甚至只有一个人，这个东西也不可或缺，否则日后发生问题那才要命
　　以前的数据字典都要单独拿出时间来进行整理，但问题多多，最明显的就是和数据结构的变化不同步，而且耗时费力，效果底下
　　但稍微有点责任心的数据库维护人员，在编辑数据库对象时，都会习惯性的编写备注描述
　　以前SQL2K时，表备注、字段备注都是直接写在名称后面，SQL会将这些信息保存到系统表：sysproperties
　　但到了SQL2005，这些备注都转移到了扩展属性里，类似的，SQL会将这些信息保存到系统表：sys.extended_properties
　　填写表扩展属性的截图：

　　在SSMS里，在表或者字段上右键，选“属性”，都可以看到“扩展属性”页，其中：
　　属性名称建议填写固定值：MS_Description，据说这样可以兼容其他的数据字典工具，方便其提取
　　属性值可以填写表或者字段的详细备注信息
　　可以为一个表或者字段添加多个扩展属性。
　　扩展属性可以跟随数据库备份及还原操作进行传递与分发
　　那么，如果已经填写了扩展属性，该如何自动生成数据字典呢？
　　首先需要对SSMS输出的文本格式进行一下变动：
　　不要选中：在结果集中包括列标题，如图：

　　
　　然后，新建查询窗口，并选择：以文本格式显示结果，如图：

　　
　　重点来了，复制以下的T-SQL脚本，并执行：



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
　　
　　执行完成后，会在结果窗口中打印出一大段HTML代码
　　复制这段HTML代码，新建一个.htm的WEB文件，粘贴进去，用浏览器打开即可阅读最新版的数据字典！
　　最终效果截图：