生成数据库字典_生成数据库字典 .tablebox tr:hover { background:#c0dbf7; }-CSDN博客

SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Author: <mrlm>
-- Create date: <2015-3-13>
-- 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>';