需求
我们在项目验收时一般会需要很多的文件，其中有一项就是 数据库的数据字典文档，平常的数据字典字典文档一般都是word或者excel文档，这里我们重点使用sql脚本文成生成html代码的形式生成数据字典，体现稍微的高级感。
工具
使用的工具为Microsoft SQL Server Management Studio
如果使用navicate可能会有乱码现象。
实现方式
方法一.通过sql脚本生成，html代码生成
第一步：在sql server中运行一下脚本
-- =============================================
-- 第一步：选择需要生成的数据库
-- 第二步：如果需要不显示某些表，需要在-- 去除不需要显示的表名称位置去除相应的表
-- 第三步：如果只想设置一个表的信息，只需要修改第二步中的条件部分
-- =============================================
USE ZFCG_20200806
go
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
-- =============================================
-- Description: <生成数据库字典>
-- =============================================
SET NOCOUNT ON;
/*
*输出头部信息
*/
PRINT '<!DOCTYPE html';
PRINT '<html lang=ZH>';
PRINT ' <head>';
PRINT ' <meta charset="UTF-8">';
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),
tableDetail VARCHAR(300)
);
INSERT INTO @tableDBTABLE
(tableName,tableDetail )
-- 查询出所有的表名称，和表备注
select DISTINCT obj.name,CONVERT(nvarchar,(case when ext.value is null then '没有备注' else ext.value end)) as tableDetail
from sysobjects obj
left join sys.extended_properties ext
on obj.id=ext.major_id and ext.minor_id=0
where obj.xtype='U'
-- 去除不需要显示的表名称
and obj.name not in('Sheet1$','T_BAS_EXPERT_INFO_2015_7_24','T_BAS_HOLIDAY_20170110','T_BAS_HOLIDAY20181228','T_BAS_PURCHASE_PERSON_INFO_2016_04_27','T_BAS_EXPERT_INFO_20210122','T_BAS_SUPPLIER_INFO_20210122')
order by obj.name ;
--输出表目录信息
DECLARE @tname_cur VARCHAR(200);
DECLARE @tname_detail 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 id="div_list" style="text-align:left; margin:20px 0px 50px 50px;"> 目录：<br><ol>';
WHILE @i_t1 <= @count_t1
BEGIN
SELECT @tname_cur = tableName,
@tname_detail = tableDetail
FROM @tableDBTABLE
WHERE id = @i_t1;
PRINT '<li><a href="#' + @tname_cur + '">表：' + @tname_cur+' --- '+@tname_detail
+ '</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,@tname_detail = tableDetail
FROM @tableDBTABLE
WHERE id = @i_t1;
--输出表头部信息
PRINT ' <div class="tableBox" id="'+@tname_cur+'">';
PRINT '<a name="' + @tname_cur + '"></a>';
PRINT ' <h3>' + CAST(@i_t1 AS VARCHAR(10)) + '.' + @tname_cur
+ ' 表 ' + @tname_detail + '</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=""><xmp style="white-space: normal;">'
+ ISNULL(CAST(exprop.value AS VARCHAR(4000)), '') -- 描述
+ '</xmp></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;
第二步：在sqlserver中执行这个sql脚本，会生成html代码
把代码粘贴到txt文件中，并把txt文件的后缀改为html，双击后就可以在浏览器中看到生成数据字典了，此处由于生成的内容太大，不做展示，效果图如下。
方法二.通过sql脚本查询sql结果生成
------sqlserver 查询某个表的列名称、说明、备注、类型等
USE [ndztb20210104]
------查询此库下的所有表
--select * from sysobjects where xtype='U' order by name;
------查询此库下的所有表，以及表详情
SELECT
表名 = case when a.colorder=1 then d.name else '' end,
表说明 = case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号 = a.colorder,
字段名 = a.name,
标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,
类型 = b.name,
占用字节数 = a.length,
长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空 = case when a.isnullable=1 then '√'else '' end,
默认值 = isnull(e.text,''),
字段说明 = isnull(g.[value],'')
FROM
syscolumns a --所有的列
left join
systypes b --字段类型
on
a.xusertype=b.xusertype
inner join
sysobjects d --表名
on
a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join
syscomments e -- 默认值
on
a.cdefault=e.id
left join
sys.extended_properties g --列备注
on
a.id=G.major_id and a.colid=g.minor_id
left join
sys.extended_properties f --表备注
on
d.id=f.major_id and f.minor_id=0
--如果只查询指定表,加上此where条件，tablename是要查询的表名；去除where条件查询所有的表信息
--where d.name='T_BAS_STOCK_LIST_CLASS_HISTORY_OLD'
order by
a.id,a.colorder```
## 附件信息
以下下一些关于本功能的系统表
```bash
use ndztb20210104
-- 查询所有的表名
select * from sysobjects where xtype='U' ;
--查询所有的表名 与上表返回的数据大体一致，但是字段名称不一样
select * from sys.tables;
--所有的列
select * from syscolumns ;
--列的类型
select * from systypes;
--默认值
select * from syscomments;
--查询表备注
select * from sys.extended_properties where class=1 and minor_id=0 ;
--查询列备注
select * from sys.extended_properties where minor_id!=0 ;
--所有的列
select * from sys.all_columns;
--查询表名和表备注名称
select DISTINCT obj.name,CONVERT(nvarchar,(case when ext.value is null then '' else ext.value end)) as tableDetail from sysobjects obj left join sys.extended_properties ext on obj.id=ext.major_id and ext.minor_id=0 where obj.xtype='U' order by obj.name ;
--查询列信息
SELECT
CAST (clmns.name AS VARCHAR(35)),
-- 列名
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)
),
-- 小数位数
ISNULL(
CAST (
cnstr.definition AS VARCHAR (20)
),
''
),
-- 默认值
(
CASE
WHEN clmns.is_nullable = 1 THEN
'√' -- 允许为空
ELSE
''
END
),
(
CASE
WHEN clmns.is_computed = 1 THEN
'√' -- 外键
ELSE
''
END
),
(
CASE
WHEN clmns.is_identity = 1 THEN
'√' -- 主键
ELSE
''
END
),
ISNULL(
CAST (exprop. VALUE AS VARCHAR(500)),
''
) -- 描述
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 = 'ACT_EVT_LOG ' -- AND exprop.class = 1
)
ORDER BY
clmns.column_id ASC;
820
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
