查看: 251|回复: 0

[SQLServer] MS SQL生成数据库字典脚本

发表于 2017-12-7 08:00:01

  开发一个项目时都会有一个蛋疼的问题——写数据库需求文档,然后根据这个文档来建数据库,如果后来需求改了,要改数据库还要改文档,有时忙着忙着就忘改了,导致文档是过期的。那么我们自己写个脚本在数据库运行直接生产数据字典,这样只要改数据库就行了。目前在网上搜了下,发现sqlServer只有2005的生成工具,没有08的,存储过程倒是有,不过下载运行一遍到处是坑,写的也太差了,于是对脚本进行改进。

  1. -- =============================================
  2. -- Author: <marlon>
  3. -- Create date: <2017-11-13>
  4. -- Description: <生成数据库字典>
  5. -- =============================================
  6. BEGIN
  7. DECLARE @TableName nvarchar(35),@htmls varchar(8000)
  8. DECLARE @字段名称 VARCHAR(200)
  9. DECLARE @类型 VARCHAR(200)
  10. DECLARE @长度 VARCHAR(200)
  11. DECLARE @数值精度 VARCHAR(200)
  12. DECLARE @小数位数 VARCHAR(200)
  13. DECLARE @默认值 VARCHAR(200)
  14. DECLARE @允许为空 VARCHAR(200)
  15. DECLARE @外键 VARCHAR(200)
  16. DECLARE @主键 VARCHAR(200)
  17. DECLARE @描述 VARCHAR(200)
  18. SET NOCOUNT ON;
  19. DECLARE Tbls CURSOR
  20. FOR
  21. Select distinct Table_name
  22. FROM INFORMATION_SCHEMA.COLUMNS
  23. order by Table_name
  24. OPEN Tbls
  25. PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
  26. PRINT '<html xmlns="http://www.w3.org/1999/xhtml">'
  27. PRINT ' <head>'
  28. PRINT ' <title>SqlServer数据字典</title>'
  29. PRINT ' <style type="text/css">'
  30. PRINT ' body{margin:0; font:11pt "arial", "微软雅黑"; cursor:default;}'
  31. PRINT ' .titleHead{margin:0 auto;text-align:center;}'
  32. PRINT ' .tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'
  33. 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 }'
  34. PRINT ' .tableBox table {width:1000px; padding:0px }'
  35. 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 }'
  36. 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 }'
  37. PRINT ' .tableBox td {text-align:center}'
  38. PRINT ' </style>'
  39. PRINT ' </head>'
  40. PRINT ' <body>'
  41. PRINT ' <div class="titleHead">'
  42. PRINT ' <h1>'+DB_NAME()+'数据字典</h1>'
  43. PRINT ' </div>'
  44. FETCH NEXT FROM Tbls INTO @TableName
  45. WHILE @@FETCH_STATUS = 0
  46. BEGIN
  47. set @htmls = ''
  48. Select @htmls = ' <h3>' + @TableName + ' : '+ CAST(Value as varchar(1000)) + '</h3>'
  49. FROM sys.extended_properties AS A
  50. WHERE A.major_id = OBJECT_ID(@TableName)
  51. and minor_id = 0
  52. if @htmls is null or DATALENGTH (@htmls)=0
  53. begin
  54. set @htmls = ' <h3>' + @TableName + '</h3>'
  55. end
  56. PRINT ' <div class="tableBox">'
  57. PRINT @htmls
  58. PRINT ' <table cellspacing="0">'
  59. PRINT ' <tr>'
  60. PRINT ' <th>字段名称</th>'
  61. PRINT ' <th>类型</th>'
  62. PRINT ' <th>长度</th>'
  63. PRINT ' <th>数值精度</th>'
  64. PRINT ' <th>小数位数</th>'
  65. PRINT ' <th>默认值</th>'
  66. PRINT ' <th>是否允许为空</th>'
  67. PRINT ' <th>是否外键</th>'
  68. PRINT ' <th>是否主键</th>'
  69. PRINT ' <th>描述</th>'
  70. PRINT ' </tr>'
  71. DECLARE TRows CURSOR
  72. FOR
  73. SELECT
  74. ' <td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>',
  75. ' <td>' + CAST(udt.name AS CHAR(15)) + '</td>' ,
  76. ' <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>',
  77. ' <td>' + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) + '</td>',
  78. ' <td>' + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) + '</td>',
  79. ' <td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>',
  80. ' <td>' + (case when clmns.is_nullable > 0 then '是' else '否' end) + '</td>' ,
  81. ' <td>' + (case when clmns.is_computed > 0 then '是' else '否' end) + '</td>' ,
  82. ' <td>' + (case when clmns.is_identity > 0 then '是' else '否' end) + '</td>' ,
  83. ' <td>' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '</td>'
  84. FROM sys.tables AS tbl
  85. INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
  86. LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key
  87. 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
  88. LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
  89. 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
  90. LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id=clmns.default_object_id
  91. 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'
  92. WHERE (tbl.name = @TableName)
  93. ORDER BY clmns.column_id ASC
  94. OPEN TRows
  95. FETCH NEXT FROM TRows INTO @字段名称,@类型,@长度,@数值精度,@小数位数,@默认值,@允许为空,@外键,@主键,@描述
  96. WHILE @@FETCH_STATUS = 0
  97. BEGIN
  98. PRINT ' <tr>'
  99. PRINT @字段名称
  100. PRINT @类型
  101. PRINT @长度
  102. PRINT @数值精度
  103. PRINT @小数位数
  104. PRINT @默认值
  105. PRINT @允许为空
  106. PRINT @外键
  107. PRINT @主键
  108. PRINT @描述
  109. PRINT ' </tr>'
  110. FETCH NEXT FROM TRows INTO @字段名称,@类型,@长度,@数值精度,@小数位数,@默认值,@允许为空,@外键,@主键,@描述
  111. END
  112. CLOSE TRows
  113. DEALLOCATE TRows
  114. PRINT ' </table>'
  115. PRINT ' </div>'
  116. FETCH NEXT FROM Tbls INTO @TableName
  117. END
  118. PRINT ' </body>'
  119. PRINT '</html>'
  120. CLOSE Tbls
  121. DEALLOCATE Tbls
  122. END
复制代码

  注意事项:

  1、以上脚本直接在SQL SERVER运行,以文本格式显示,设置如下:

  

  红框的不要勾选

2、如果要加注释,表的注释在这里加

字段的注释:

3、生成后保存为HTML文件即可

效果展示:

  



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条