mssql生成数据库字典脚本
解决问题：
1、数据库字段变动每次都需要修改字典。
2、开发中其他原因导致遗忘修改字典。
解决方案
1、脚本
1 SET ANSI_NULLS ON; 2 GO 3 SET QUOTED_IDENTIFIER ON; 4 GO 5 -- ============================================= 6 -- Author: <mrlm> 7 -- Create date: <2015-3-13> 8 -- Description: <生成数据库字典> 9 -- ============================================= 10 11 SET NOCOUNT ON; 12 /* 13 *输出头部信息 14 */ 15 PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'; 16 PRINT '<html xmlns="http://www.w3.org/1999/xhtml">'; 17 PRINT ' <head>'; 18 PRINT ' <title>数据库字典</title>'; 19 PRINT ' <style type="text/css">'; 20 PRINT ' body{margin:0;cursor:default;font-family: Arial, Helvetica, sans-serif, "微软雅黑" , "宋体";}'; 21 PRINT ' .tableBox{margin:10px auto; padding:0px; width:1200px; height:auto; background:#EAEFF3; border:1px solid #45360A; margin-top:50px;}'; 22 PRINT ' .tableBox h3 {font-size:12pt; height:30px; line-height:30px; background:#460B6A; padding:0px 0px 0px 15px; color:#FFF; margin:0px; text-align:left }'; 23 PRINT ' .tableBox table {width:1200px; padding:0px }'; 24 PRINT ' .tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#C0DBF7; font-size:14px; }'; 25 PRINT ' .tableBox td {font-size: 12px;height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; }'; 26 PRINT ' .tableBox tr:hover { background:#C0DBF7; } '; 27 PRINT ' .tableBox .field{font-weight:bold;color:#276926;}'; 28 PRINT ' </style>'; 29 PRINT ' </head>'; 30 PRINT ' <body>'; 31 PRINT '<div style="text-align:center;"><h2>' + DB_NAME() 32 + ' 数据库字典</h2></br><font style="color:gray;">生成于：' 33 + CONVERT(VARCHAR, GETDATE(), 120) + '</font></div></br>'; 34 35 /* 36 *数据库菜单列表 37 */ 38 --数据库表 39 DECLARE @tableDBTABLE TABLE 40 ( 41 id INT IDENTITY(1, 1) 42 PRIMARY KEY , 43 tableName NVARCHAR(300) 44 ); 45 46 47 INSERT INTO @tableDBTABLE 48 ( tableName 49 ) 50 SELECT DISTINCT 51 TABLE_NAME AS tableName 52 FROM INFORMATION_SCHEMA.COLUMNS 53 WHERE ( SELECT COUNT(*) 54 FROM sys.tables AS tbl 55 INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id 56 LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id 57 AND 1 = idx.is_primary_key 58 LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id 59 AND idxcol.column_id = clmns.column_id 60 AND idxcol.object_id = clmns.object_id 61 AND 0 = idxcol.is_included_column 62 LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id 63 LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id 64 AND typ.user_type_id = typ.system_type_id 65 LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id 66 LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id 67 AND exprop.minor_id = clmns.column_id 68 AND exprop.name = 'MS_Description' 69 WHERE ( tbl.name = TABLE_NAME 70 AND exprop.class = 1 71 ) 72 ) > 0 73 ORDER BY TABLE_NAME; 74 75 --输出表目录信息 76 DECLARE @tname_cur VARCHAR(200); 77 DECLARE @count_t1 INT; 78 DECLARE @i_t1 INT; 79 DECLARE @i_t2 INT; 80 81 DECLARE @字段名称 VARCHAR(200); 82 DECLARE @类型 VARCHAR(200); 83 DECLARE @长度 VARCHAR(200); 84 DECLARE @数值精度 VARCHAR(200); 85 DECLARE @小数位数 VARCHAR(200); 86 DECLARE @默认值 VARCHAR(200); 87 DECLARE @允许为空 VARCHAR(200); 88 DECLARE @外键 VARCHAR(200); 89 DECLARE @主键 VARCHAR(200); 90 DECLARE @描述 VARCHAR(4000); 91 92 --初始化值 93 SET @i_t1 = 1; 94 SET @i_t2 = 1; 95 SELECT @count_t1 = COUNT(*) 96 FROM @tableDBTABLE; 97 98 99 IF @count_t1 > 0 100 BEGIN 101 PRINT '<div style="text-align:left; margin:20px 0px 50px 50px;"> 目录：<br><ol>'; 102 WHILE @i_t1 <= @count_t1 103 BEGIN 104 SELECT @tname_cur = tableName 105 FROM @tableDBTABLE 106 WHERE id = @i_t1; 107 PRINT '<li><a href="#' + @tname_cur + '">表：' + @tname_cur 108 + '</a></li>'; 109 SET @i_t1 = @i_t1 + 1; 110 END; 111 PRINT '</ol></div>'; 112 END; 113 --输出表行信息 114 IF @count_t1 > 0 115 BEGIN 116 SET @i_t1 = 1; 117 WHILE @i_t1 <= @count_t1 118 BEGIN 119 SELECT @tname_cur = tableName 120 FROM @tableDBTABLE 121 WHERE id = @i_t1; 122 123 DECLARE @tabledesc NVARCHAR(4000);--表描述 124 SELECT @tabledesc = CAST(value AS VARCHAR(4000)) 125 FROM sys.extended_properties AS A 126 WHERE A.major_id = OBJECT_ID(@tname_cur) 127 AND name = 'MS_Description' 128 AND minor_id = 0; 129 SET @tabledesc = CASE WHEN ( @tabledesc IS NULL 130 OR LEN(RTRIM(LTRIM(@tabledesc))) <= 0 131 ) THEN ' ' 132 ELSE ' : ' + @tabledesc 133 END; 134 135 --输出表头部信息 136 PRINT ' <div class="tableBox">'; 137 PRINT '<a name="' + @tname_cur + '"></a>'; 138 PRINT ' <h3>' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur 139 + ' 表 ' + @tabledesc + '</h3>'; 140 PRINT ''; 141 142 PRINT ' <table cellspacing="0">'; 143 PRINT ' <tr>'; 144 PRINT ' <th>序号</th>'; 145 PRINT ' <th>字段名称</th>'; 146 PRINT ' <th>类型</th>'; 147 PRINT ' <th>长度</th>'; 148 PRINT ' <th>数值精度</th>'; 149 PRINT ' <th>小数位数</th>'; 150 PRINT ' <th>默认值</th>'; 151 PRINT ' <th>允许为空</th>'; 152 PRINT ' <th>外键</th>'; 153 PRINT ' <th>主键</th>'; 154 PRINT ' <th>描述</th>'; 155 PRINT ' </tr>'; 156 157 DECLARE TRows CURSOR 158 FOR 159 SELECT ' <td class="field">' 160 + CAST(clmns.name AS VARCHAR(35)) + '</td>' , 161 ' <td>' + CAST(udt.name AS CHAR(15)) + '</td>' , 162 ' <td>' 163 + CAST(CAST(CASE WHEN typ.name IN ( N'nchar', 164 N'nvarchar' ) 165 AND clmns.max_length <> -1 166 THEN clmns.max_length / 2 167 ELSE clmns.max_length 168 END AS INT) AS VARCHAR(20)) + '</td>' , 169 ' <td>' 170 + CAST(CAST(clmns.precision AS INT) AS VARCHAR(20)) 171 + '</td>' , 172 ' <td>' 173 + CAST(CAST(clmns.scale AS INT) AS VARCHAR(20)) 174 + '</td>' , 175 ' <td>' 176 + ISNULL(CAST(cnstr.definition AS VARCHAR(20)), '') 177 + '</td>' , 178 ' <td>' 179 + ( CASE WHEN clmns.is_nullable = 1 THEN '√' 180 ELSE '' 181 END ) + '</td>' , 182 ' <td>' 183 + ( CASE WHEN clmns.is_computed = 1 THEN '√' 184 ELSE '' 185 END ) + '</td>' , 186 ' <td>' 187 + ( CASE WHEN clmns.is_identity = 1 THEN '√' 188 ELSE '' 189 END ) + '</td>' , 190 ' <td style="">' 191 + ISNULL(CAST(exprop.value AS VARCHAR(500)), '') 192 + '</td>' 193 FROM sys.tables AS tbl 194 INNER JOIN sys.all_columns AS clmns ON clmns.object_id = tbl.object_id 195 LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id 196 AND 1 = idx.is_primary_key 197 LEFT OUTER JOIN sys.index_columns AS idxcol ON idxcol.index_id = idx.index_id 198 AND idxcol.column_id = clmns.column_id 199 AND idxcol.object_id = clmns.object_id 200 AND 0 = idxcol.is_included_column 201 LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id 202 LEFT OUTER JOIN sys.types AS typ ON typ.user_type_id = clmns.system_type_id 203 AND typ.user_type_id = typ.system_type_id 204 LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id = clmns.default_object_id 205 LEFT OUTER JOIN sys.extended_properties exprop ON exprop.major_id = clmns.object_id 206 AND exprop.minor_id = clmns.column_id 207 AND exprop.name = 'MS_Description' 208 WHERE ( tbl.name = @tname_cur 209 AND exprop.class = 1 210 ) 211 ORDER BY clmns.column_id ASC; 212 213 SET @i_t2 = 1; 214 OPEN TRows; 215 FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度, @小数位数, @默认值, 216 @允许为空, @外键, @主键, @描述; 217 218 --输出表行数据 219 WHILE @@FETCH_STATUS = 0 220 BEGIN 221 PRINT ' <tr>'; 222 PRINT ' <td>' + CAST(@i_t2 AS VARCHAR(10)) + '</td>'; 223 PRINT @字段名称; 224 PRINT @类型; 225 PRINT @长度; 226 PRINT @数值精度; 227 PRINT @小数位数; 228 PRINT @默认值; 229 PRINT @允许为空; 230 PRINT @外键; 231 PRINT @主键; 232 PRINT @描述; 233 PRINT ' </tr>'; 234 FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度, 235 @小数位数, @默认值, @允许为空, @外键, @主键, @描述; 236 SET @i_t2 = @i_t2 + 1; 237 END; 238 CLOSE TRows; 239 DEALLOCATE TRows; 240 PRINT '<tr><td colspan="11" style="text-align:right"><a href="#top">TOP↑</a></td></tr>'; 241 PRINT ' </table>'; 242 PRINT ' </div>'; 243 244 SET @i_t1 = @i_t1 + 1; 245 END; 246 END; 247 PRINT ' </body>'; 248 PRINT '</html>'; 249 SET NOCOUNT OFF;
2、添加注释
2.1、表注释
2.2、字段注释
3、运行脚本生成html脚本，复制脚本保存到html文件中打开就是数据字典了
展示效果
浙公网安备 33010602011771号