Sqlserver 导出表数据类型和字段详细信息
USE [Database Name] go SET ANSI_NULLS ON; GO SET QUOTED_IDENTIFIER ON; GO -- ============================================= -- Description: <生成数据库字典> -- ============================================= SET NOCOUNT ON; /* *输出头部信息 */ PRINT '<!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 ' <style type="text/css">'; PRINT ' body{margin:0;cursor:default;font-family: Arial, Helvetica, sans-serif, "微软雅黑" , "宋体";}'; PRINT ' .tableBox{margin:10px auto; padding:0px; width:1200px; height:auto; background:#EAEFF3; border:1px solid #45360A; margin-top:50px;}'; 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 }'; PRINT ' .tableBox table {width:1200px; padding:0px }'; PRINT ' .tableBox th {height:25px; border-top:1px solid #FFF; border-left:1px solid #FFF; background:#C0DBF7; font-size:14px; }'; PRINT ' .tableBox td {font-size: 12px;height:25px; padding-left:10px; border-top:1px solid #FFF; border-left:1px solid #FFF; }'; PRINT ' .tableBox tr:hover { background:#C0DBF7; } '; PRINT ' .tableBox .field{font-weight:bold;color:#276926;}'; PRINT ' </style>'; PRINT ' </head>'; PRINT ' <body>'; PRINT '<div style="text-align:center;"><h2>' + DB_NAME() + ' 数据库字典</h2></br><font style="color:gray;">生成于：' + CONVERT(VARCHAR, GETDATE(), 120) + '</font></div></br>'; /* *数据库菜单列表 */ --数据库表 DECLARE @tableDBTABLE TABLE ( id INT IDENTITY(1, 1) PRIMARY KEY , tableName NVARCHAR(300) ); INSERT INTO @tableDBTABLE ( tableName ) SELECT DISTINCT TABLE_NAME AS tableName FROM INFORMATION_SCHEMA.COLUMNS WHERE ( SELECT COUNT(*) 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 = TABLE_NAME --AND exprop.class = 1 ) ) > 0 ORDER BY TABLE_NAME; --输出表目录信息 DECLARE @tname_cur VARCHAR(200); DECLARE @count_t1 INT; DECLARE @i_t1 INT; DECLARE @i_t2 INT; DECLARE @字段名称 VARCHAR(200); DECLARE @类型 VARCHAR(200); DECLARE @长度 VARCHAR(200); DECLARE @数值精度 VARCHAR(200); DECLARE @小数位数 VARCHAR(200); DECLARE @默认值 VARCHAR(200); DECLARE @允许为空 VARCHAR(200); DECLARE @外键 VARCHAR(200); DECLARE @主键 VARCHAR(200); DECLARE @描述 VARCHAR(4000); --初始化值 SET @i_t1 = 1; SET @i_t2 = 1; SELECT @count_t1 = COUNT(*) FROM @tableDBTABLE; IF @count_t1 > 0 BEGIN PRINT '<div style="text-align:left; margin:20px 0px 50px 50px;"> 目录：<br><ol>'; WHILE @i_t1 <= @count_t1 BEGIN SELECT @tname_cur = tableName FROM @tableDBTABLE WHERE id = @i_t1; PRINT '<li><a href="#' + @tname_cur + '">表：' + @tname_cur + '</a></li>'; SET @i_t1 = @i_t1 + 1; END; PRINT '</ol></div>'; END; --输出表行信息 IF @count_t1 > 0 BEGIN SET @i_t1 = 1; WHILE @i_t1 <= @count_t1 BEGIN SELECT @tname_cur = tableName FROM @tableDBTABLE WHERE id = @i_t1; DECLARE @tabledesc NVARCHAR(4000);--表描述 SELECT @tabledesc = CAST(value AS VARCHAR(4000)) FROM sys.extended_properties AS A WHERE A.major_id = OBJECT_ID(@tname_cur) AND name = 'MS_Description' AND minor_id = 0; SET @tabledesc = CASE WHEN ( @tabledesc IS NULL OR LEN(RTRIM(LTRIM(@tabledesc))) <= 0 ) THEN ' ' ELSE ' : ' + @tabledesc END; --输出表头部信息 PRINT ' <div class="tableBox">'; PRINT '<a name="' + @tname_cur + '"></a>'; PRINT ' <h3>' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur + ' 表 ' + @tabledesc + '</h3>'; PRINT ''; PRINT ' <table cellspacing="0">'; PRINT ' <tr>'; 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 ' </tr>'; DECLARE TRows CURSOR FOR SELECT ' <td class="field">' + CAST(clmns.name AS VARCHAR(35)) + '</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>' + ISNULL(CAST(cnstr.definition AS VARCHAR(20)), '') + '</td>' , ' <td>' + ( CASE WHEN clmns.is_nullable = 1 THEN '√' ELSE '' END ) + '</td>' , ' <td>' + ( CASE WHEN clmns.is_computed = 1 THEN '√' ELSE '' END ) + '</td>' , ' <td>' + ( CASE WHEN clmns.is_identity = 1 THEN '√' ELSE '' END ) + '</td>' , ' <td style="">' + ISNULL(CAST(exprop.value AS VARCHAR(500)), '') + '</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 = @tname_cur --AND exprop.class = 1 ) ORDER BY clmns.column_id ASC; SET @i_t2 = 1; OPEN TRows; FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度, @小数位数, @默认值, @允许为空, @外键, @主键, @描述; --输出表行数据 WHILE @@FETCH_STATUS = 0 BEGIN PRINT ' <tr>'; PRINT ' <td>' + CAST(@i_t2 AS VARCHAR(10)) + '</td>'; PRINT @字段名称; PRINT @类型; PRINT @长度; PRINT @数值精度; PRINT @小数位数; PRINT @默认值; PRINT @允许为空; PRINT @外键; PRINT @主键; PRINT @描述; PRINT ' </tr>'; FETCH NEXT FROM TRows INTO @字段名称, @类型, @长度, @数值精度, @小数位数, @默认值, @允许为空, @外键, @主键, @描述; SET @i_t2 = @i_t2 + 1; END; CLOSE TRows; DEALLOCATE TRows; PRINT '<tr><td colspan="11" style="text-align:right"><a href="#top">TOP↑</a></td></tr>'; PRINT ' </table>'; PRINT ' </div>'; SET @i_t1 = @i_t1 + 1; END; END; PRINT ' </body>'; PRINT '</html>'; SET NOCOUNT OFF;
运气之后，改成后缀未".HTML"运行即可
