SQL语句生成数据字典

最新推荐文章于 2024-01-01 12:24:15 发布

weixin_30455661

最新推荐文章于 2024-01-01 12:24:15 发布

阅读量666

点赞数

文章标签： xhtml 数据库

原文链接：http://www.cnblogs.com/hezhijie/articles/5151230.html

版权

通过系统表记录信息自动生成html。

页面效果如下：

SQL代码

  1 BEGIN
  2     DECLARE @TableName nvarchar(35),@tempHtml nvarchar(max),@htmls NVARCHAR(max)=''
  3     DECLARE @字段名称 NVARCHAR(200)
  4     DECLARE @类型  NVARCHAR(200)
  5     DECLARE @长度 VARCHAR(200)
  6    -- DECLARE @小数位数 VARCHAR(200)
  7     DECLARE @默认值 NVARCHAR(200)
  8     DECLARE @允许为空 VARCHAR(200)
  9     DECLARE @外键 VARCHAR(200)
 10     DECLARE @索引 VARCHAR(200)
 11     DECLARE @主键 VARCHAR(200)
 12     DECLARE @描述 NVARCHAR(200)
 13     DECLARE @数据库名 NVARCHAR(200)=DB_NAME()
 14     SET NOCOUNT ON;
 15     
 16 
 17     DECLARE Tbls CURSOR
 18     FOR
 19         Select distinct Table_name
 20         FROM INFORMATION_SCHEMA.COLUMNS
 21         order by Table_name
 22     OPEN Tbls
 23         PRINT '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">'
 24         PRINT '<html xmlns="http://www.w3.org/1999/xhtml">'
 25         PRINT '    <head>'
 26         PRINT '        <title>'+@数据库名+'-数据库字典</title>'
 27         PRINT '        <style type="text/css">'
 28         PRINT '            body{margin:0; font:11pt "arial", "微软雅黑"; cursor:default;}'
 29         PRINT '            .tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}'
 30         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 }'
 31         PRINT '            .tableBox table {width:1000px; padding:0px }'
 32         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 }'
 33         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 }'
 34         PRINT '        </style>'
 35         PRINT '    </head>'
 36         PRINT '    <body>'
 37     FETCH NEXT FROM Tbls INTO @TableName
 38     WHILE @@FETCH_STATUS = 0
 39     BEGIN
 40         SET @tempHtml='';
 41         Select @tempHtml = '        
 42         <h3>' + @TableName + ' : '+ CAST(Value as varchar(1000)) 
 43         +'        （创建时间:'+(SELECT  CONVERT(VARCHAR(20),create_date,20) FROM sys.objects  WHERE name=@TableName)
 44         +',修改时间:'+(SELECT  CONVERT(VARCHAR(20),modify_date,20) FROM sys.objects  WHERE name=@TableName)
 45         + ')</h3>'
 46         FROM sys.extended_properties AS A
 47         WHERE A.major_id = OBJECT_ID(@TableName)
 48         and name = 'MS_Description' and minor_id = 0
 49         PRINT '<div class="tableBox">'
 50         IF @tempHtml=''
 51             Select @tempHtml = '        
 52         <h3>' + @TableName 
 53         +'        （创建时间:'+(SELECT  CONVERT(VARCHAR(20),create_date,20) FROM sys.objects  WHERE name=@TableName)
 54         +',修改时间:'+(SELECT  CONVERT(VARCHAR(20),modify_date,20) FROM sys.objects  WHERE name=@TableName)
 55         + ')</h3>'
 56        
 57         PRINT @tempHtml
 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 '</tr>'
 70         
 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.scale AS INT) AS VARCHAR(20)) + '</td>',
 78             '<td>' + ISNULL(CAST(cnstr.definition AS VARCHAR(20)),'') + '</td>',
 79             '<td>' + ( CASE WHEN clmns.is_nullable=1 THEN '是' ELSE '否' END ) + '</td>' ,
 80             '<td>' + 
 81              ( 
 82             SELECT  ISNULL(( SELECT TOP 1
 83                         i.name
 84                  FROM   sys.indexes i
 85                         INNER JOIN sys.objects o ON o.type = 'U'
 86                                                     AND tbl.object_id = o.object_id
 87                                                     AND i.type_desc <> 'HEAP'
 88                                                     AND o.object_id = i.object_id
 89                                                     
 90                         INNER JOIN sys.index_columns ic ON i.object_id = ic.object_id
 91                                                            AND ic.column_id = clmns.column_id
 92                                                            AND i.index_id = ic.index_id
 93                         INNER JOIN sys.columns c ON c.object_id = o.object_id
 94                                                     AND c.column_id = ic.column_id
 95                ), '')
 96              ) + '</td>' ,
 97             '<td>' + ( CASE WHEN clmns.is_identity=1 THEN '是' ELSE '否' END ) + '</td>' ,
 98             '<td>' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '</td>'
 99             FROM sys.tables AS tbl 
100             INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
101             LEFT OUTER JOIN sys.indexes AS idx ON idx.object_id = clmns.object_id AND 1 =idx.is_primary_key
102             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
103             LEFT OUTER JOIN sys.types AS udt ON udt.user_type_id = clmns.user_type_id
104             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
105             LEFT JOIN sys.default_constraints AS cnstr ON cnstr.object_id=clmns.default_object_id
106             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'
107             WHERE (tbl.name = @TableName ) --and exprop.class = 1I don't wand to include comments on indexes
108             ORDER BY clmns.column_id ASC
109         OPEN TRows
110         FETCH NEXT FROM TRows INTO @字段名称,@类型,@长度,
111         --@小数位数,
112         @默认值,@允许为空,@索引,@主键,@描述
113         WHILE @@FETCH_STATUS = 0
114         BEGIN
115             PRINT '<tr>'
116             PRINT @字段名称
117             PRINT @类型
118             PRINT @长度
119             --PRINT @小数位数
120             PRINT @默认值
121             PRINT @允许为空
122             PRINT @索引
123             PRINT @主键
124             PRINT @描述
125             PRINT '</tr>'
126             FETCH NEXT FROM TRows INTO @字段名称,@类型,@长度,--@小数位数,
127             @默认值,@允许为空,@索引,
128             @主键,@描述
129         END
130         CLOSE TRows
131         DEALLOCATE TRows
132 
133         PRINT '</table>'
134         PRINT '</div>'
135     FETCH NEXT FROM Tbls INTO @TableName
136     END
137         PRINT '</body>'
138         PRINT '</html>'
139     CLOSE Tbls
140     DEALLOCATE Tbls
141     
142     --SELECT @htmls AS html;
143     --PRINT @htmls
144     
145 END
146 GO

转载于:https://www.cnblogs.com/hezhijie/articles/5151230.html

weixin_30455661

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL语句生成数据字典

通过系统表记录信息自动生成html。页面效果如下：SQL代码 1 BEGIN 2 DECLARE @TableName nvarchar(35),@tempHtml nvarchar(max),@htmls NVARCHAR(max)='' 3 DECLARE @字段名称 NVARCHAR(200) 4 DECLARE @类型 NV...
复制链接

扫一扫