sql server导出数据字典
链接：https://pan.baidu.com/s/1yqgHZ6kfXXUGHocfTTctDA
提取码：puon
【2】导出 chm文档工具
导出工具（三大数据库都可用）
链接：https://pan.baidu.com/s/1slCt1Quc1WNXba3KLIoMlQ
提取码：235q
【3】保存数据字典成 ｈｔｍｌ
【3.1】打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。
直接在表上或者字段上右键属性-扩展属性，添加一个key-value对，key就是“MS_Description",value就是你自己的描述。详细见下图：
给字段添加描述的过程与上述一致，不在赘述。
【3.2】执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。
Set nocount on DECLARE @TableName nvarchar(35) DECLARE Tbls CURSOR FOR Select distinct Table_name FROM INFORMATION_SCHEMA.COLUMNS --put any exclusions here --where table_name not like '%old' order by Table_name OPEN Tbls 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; font:11pt "arial", "微软雅黑"; cursor:default;}' PRINT '.tableBox{margin:10px auto; padding:0px; width:1000px; height:auto; background:#FBF5E3; border:1px solid #45360A}' 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 }' PRINT '.tableBox table {width:1000px; padding:0px }' 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 }' 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 }' PRINT '</style>' PRINT '</head>' PRINT '<body>' FETCH NEXT FROM Tbls INTO @TableName WHILE @@FETCH_STATUS = 0 BEGIN PRINT '<div class="tableBox">' Select '<h3>表' + @TableName + ' : '+cast(Value as varchar(1000)) + '</h3>' FROM sys.extended_properties A WHERE A.major_id = OBJECT_ID(@TableName) and name = 'MS_Description' and minor_id = 0 PRINT '<table cellspacing="0">' --Get the Description of the table --Characters 1-250 PRINT '<tr>' --Set up the Column Headers for the Table 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 '<th>默认值</th>' --Get the Table Data SELECT '</tr><tr>', '<td>' + CAST(clmns.name AS VARCHAR(35)) + '</td>', '<td>' + ISNULL(CAST(exprop.value AS VARCHAR(500)),'') + '</td>', '<td>' + CAST(ISNULL(idxcol.index_column_id, 0)AS VARCHAR(20)) + '</td>', '<td>' + CAST(ISNULL( (SELECT TOP 1 1 FROM sys.foreign_key_columns AS fkclmn WHERE fkclmn.parent_column_id = clmns.column_id AND fkclmn.parent_object_id = clmns.object_id ), 0) AS VARCHAR(20)) + '</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>' + CAST(clmns.is_nullable AS VARCHAR(20)) + '</td>' , '<td>' + CAST(clmns.is_computed AS VARCHAR(20)) + '</td>' , '<td>' + CAST(clmns.is_identity AS VARCHAR(20)) + '</td>' , '<td>' + isnull(CAST(cnstr.definition AS VARCHAR(20)),'') + '</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 = @TableName and exprop.class = 1) --I don't wand to include comments on indexes ORDER BY clmns.column_id ASC PRINT '</tr></table>' PRINT '</div>' FETCH NEXT FROM Tbls INTO @TableName END PRINT '</body></HTML>' CLOSE Tbls DEALLOCATE Tbls
如下图，第二个框是”以文本方式显示结果”，不要选错了：
【3.3】最终显示效果
【4】获取带目录的html
USE [SFKD] 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;
【最佳实践】查看数据字典
查看单库字典
SELECT 表名 = CASE WHEN C.column_id = 1 THEN O.name ELSE N'' END , 表说明 = ISNULL(CASE WHEN C.column_id = 1 THEN PTB.[value] END, N'') , 字段序号 = C.column_id , 字段名 = C.name , 主键 = ISNULL(IDX.PrimaryKey, N'') , 标识 = CASE WHEN C.is_identity = 1 THEN N'√' ELSE N'' END , 计算列 = CASE WHEN C.is_computed = 1 THEN N'√' ELSE N'' END , --类型 = T.name , 类型 =CASE WHEN T.NAME IN ('CHAR','NCHAR','VARCHAR','NVARCHAR') THEN T.NAME+'('+ISNULL(CAST(case when COLUMNPROPERTY(C.OBJECT_ID,C.NAME,'PRECISION')=-1 then null else COLUMNPROPERTY(C.OBJECT_ID,C.NAME,'PRECISION') end AS VARCHAR(10)),'MAX')+')' WHEN T.NAME ='DECIMAL' THEN T.NAME+'('+CAST(COLUMNPROPERTY(C.OBJECT_ID,C.NAME,'PRECISION') AS VARCHAR(10))+','+CAST(ISNULL(COLUMNPROPERTY(C.OBJECT_ID,C.NAME,'SCALE'),0) AS VARCHAR(10))+')' ELSE T.NAME END, 长度 = C.max_length , 精度 = C.precision , 小数位数 = C.scale , 允许空 = CASE WHEN C.is_nullable = 1 THEN N'√' ELSE N'' END , 默认值 = ISNULL(D.definition, N'') , 字段说明 = ISNULL(PFD.[value], N'') , 索引名 = ISNULL(IDX.IndexName, N'') , 索引排序 = ISNULL(IDX.Sort, N'') , 创建时间 = O.Create_Date , 修改时间 = O.Modify_date FROM sys.columns C INNER JOIN sys.objects O ON C.[object_id] = O.[object_id] AND O.type = 'U' AND O.is_ms_shipped = 0 INNER JOIN sys.types T ON C.user_type_id = T.user_type_id LEFT JOIN sys.default_constraints D ON C.[object_id] = D.parent_object_id AND C.column_id = D.parent_column_id AND C.default_object_id = D.[object_id] LEFT JOIN sys.extended_properties PFD ON PFD.class = 1 AND C.[object_id] = PFD.major_id AND C.column_id = PFD.minor_id -- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述) LEFT JOIN sys.extended_properties PTB ON PTB.class = 1 AND PTB.minor_id = 0 AND C.[object_id] = PTB.major_id -- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述) LEFT JOIN -- 索引及主键信息 ( SELECT IDXC.[object_id] , IDXC.column_id , Sort = CASE INDEXKEY_PROPERTY(IDXC.[object_id], IDXC.index_id, IDXC.index_column_id, 'IsDescending') WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END , PrimaryKey = CASE WHEN IDX.is_primary_key = 1 THEN N'√' ELSE N'' END , IndexName = IDX.Name FROM sys.indexes IDX INNER JOIN sys.index_columns IDXC ON IDX.[object_id] = IDXC.[object_id] AND IDX.index_id = IDXC.index_id LEFT JOIN sys.key_constraints KC ON IDX.[object_id] = KC.[parent_object_id] AND IDX.index_id = KC.unique_index_id INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息 ( SELECT [object_id] , Column_id , index_id = MIN(index_id) FROM sys.index_columns GROUP BY [object_id] , Column_id ) IDXCUQ ON IDXC.[object_id] = IDXCUQ.[object_id] AND IDXC.Column_id = IDXCUQ.Column_id AND IDXC.index_id = IDXCUQ.index_id ) IDX ON C.[object_id] = IDX.[object_id] AND C.column_id = IDX.column_id --WHERE O.name like '%Action_CdkeyLog%' ORDER BY O.name , C.column_id
查看全库
USE master IF OBJECT_ID ( N'MetaData', N'U' ) IS NULL BEGIN CREATE TABLE [dbo].[MetaData] ( [DateBaseName] [NVARCHAR] (128) NULL , [TableName] [NVARCHAR] (128) NULL , [colorder] [SMALLINT] NULL , [name] [NVARCHAR] (128) NULL , [IsIdentity] [VARCHAR] (2) NULL , [IsPK] [VARCHAR] (2) NULL , [Style] [NVARCHAR] (128) NULL , [Bytes] [SMALLINT] NULL , [Length] [INT] NULL , [decimal] [INT] NULL , [IsNull] [VARCHAR] (2) NULL , [Default] [NVARCHAR] (4000) NULL , [Notes] [SQL_VARIANT] NULL , [last_user_update] [DATETIME] NULL , [last_user_lookup] [DATETIME] NULL , [last_user_scan] [DATETIME] NULL , [last_user_seek] [DATETIME] NULL ) ON [PRIMARY] END --获取库名 SELECT name INTO #DBName FROM sys.databases WHERE name NOT IN ( 'master', 'msdb', 'model', 'tempdb' ) and state = 0 DECLARE mycur CURSOR FOR SELECT name FROM #DBName ORDER BY name DECLARE @DBname NVARCHAR (100) DECLARE @Sql NVARCHAR (MAX) DECLARE @Sql1 NVARCHAR (MAX) DECLARE @Sql2 NVARCHAR (MAX) DECLARE @Sql3 NVARCHAR (MAX) --开启游标，遍历库名 OPEN mycur FETCH mycur INTO @DBname WHILE @@FETCH_STATUS >= 0 BEGIN -- 切库 SET @Sql = N'use ' + '['+@DBname+']' -- 获取数据字典 SET @Sql1 = N' SELECT ( CASE WHEN a.colorder = 1 THEN d.name ELSE '''' END ) TableName, a.colorder, a.name, ( CASE WHEN COLUMNPROPERTY ( a.id, a.name, ''' + 'IsIdentity' + ''' ) = 1 THEN ''' + '√' + ''' ELSE '''' END ) IsIdentity , ( CASE WHEN ( SELECT COUNT ( * ) FROM sysobjects WHERE ( name IN ( SELECT name FROM sysindexes WHERE ( id = a.id ) AND ( indid IN ( SELECT indid FROM sysindexkeys WHERE ( id = a.id ) AND ( colid IN ( SELECT colid FROM syscolumns WHERE ( id = a.id ) AND ( name = a.name ))))))) AND ( xtype = ''' + 'PK' + ''' )) > 0 THEN ''' + '√' + ''' ELSE '''' END ) AS IsPK, b.name AS Style, a.length AS Bytes, COLUMNPROPERTY ( a.id, a.name, ''' + 'PRECISION' + ''' ) AS Length, ISNULL ( COLUMNPROPERTY ( a.id, a.name, ''' + 'Scale' + ''' ), 0 ) AS decimal , ( CASE WHEN a.isnullable = 1 THEN ''' + '√' + ''' ELSE '''' END ) [IsNull], ISNULL ( e.text, '''' ) AS [Default], ISNULL ( g.[value], '''' ) AS Notes INTO #T1 FROM syscolumns a LEFT JOIN systypes b ON a.xtype = 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 ORDER BY a.id, a.colorder, TableName' -- 获取表上索引的使用状况 SET @Sql2 = N' SELECT DB_NAME ( a.[database_id] ) AS DateBaseName, OBJECT_NAME ( a.[object_id] ) AS TableName, MAX ( a.last_user_update ) AS last_user_update, MAX ( a.[last_user_lookup] ) AS last_user_lookup , MAX ( a.[last_user_scan] ) AS last_user_scan, MAX ( a.[last_user_seek] ) AS last_user_seek INTO #T2 FROM sys.dm_db_index_usage_stats AS a WHERE a.[database_id] = DB_ID () GROUP BY a.[database_id], a.[object_id]' --组合输出 SET @Sql3 = N' INSERT INTO master.dbo.metadata ( [DateBaseName], [TableName], [colorder], [name], [IsIdentity], [IsPK], [Style], [Bytes], [Length], [decimal], [IsNull], [Default], [Notes], [last_user_update] , [last_user_lookup] ,[last_user_scan], [last_user_seek] ) SELECT DateBaseName, #T1.[TableName], [colorder], [name], [IsIdentity], [IsPK], [Style], [Bytes], [Length], [decimal], [IsNull], [Default], [Notes], last_user_update, last_user_lookup , last_user_scan ,last_user_seek FROM #T1 LEFT JOIN #T2 ON #T1.TableName = #T2.TableName AND #T1.colorder = 1' EXEC ( @Sql + @Sql1 + @Sql2 + @Sql3 ) FETCH mycur INTO @DBname END CLOSE mycur DEALLOCATE mycur DROP TABLE #DBName go USE master SELECT ISNULL ( [DateBaseName], '' ) AS '库名', ISNULL ( [TableName], '' ) AS '表名', ISNULL ( [colorder], '' ) AS '字段序号', ISNULL ( [name], '' ) AS '列名', ISNULL ( [IsIdentity], '' ) AS '是否标识' , ISNULL ( [IsPK], '' ) AS '是否主键', ISNULL ( [Style], '' ) AS '类型', ISNULL ( [Bytes], '' ) AS '占空间', ISNULL ( [Length], '' ) AS '长度', ISNULL ( [decimal], '' ) AS '小数位数' , ISNULL ( [IsNull], '' ) AS '是否允许为空', ISNULL ( [Default], '' ) AS '默认值', ISNULL ( [Notes], '' ) AS '注释', [last_user_update] AS '最后更新时间', [last_user_lookup] AS '最后搜索时间' , [last_user_scan] AS '最后扫描时间', [last_user_seek] AS '最后查找时间' FROM [dbo].[MetaData]
