您现在的位置是：首页 > 后端

当前栏目

sql server导出数据字典

server 导出 SQL 数据字典

2023-09-11 14:21:09 时间

转自：https://blog.csdn.net/catshitone/article/details/53994932

关键词：数据字典，导出

【1】代码生成器，在线添加表描述

描述可以使用工具，很好用（MSSQL）

链接：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]

猜你喜欢

vim删除某一列
《Redis入门指南（第2版）》一第2章准备
VirtualBox安装OpenEuler操作系统
Windows Pe 第三章 PE头文件-EX-相关编程-2(RVA_FOA转换)
大表如何在线新增字段？
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver 错误的解决办法
zookeeper源码分析之二客户端启动
通讯录结构体方法的实现和VS中存在的一些问题的分析
boost之lexical_cast
Bean Validation
Bridging and Bonding with CentOS 6.5
Linux partprobe命令详解
【ESWIN编程大赛】一、熟悉PINT环境，借助向量相加demo快速上手
[LeetCode] Search Insert Position
Dubbo_异常_Service启动时默认将方法注册到内网IP

相关主题

Node.js http Server
PHP $_SERVER 详解
c tcp server

zl程序教程

当前栏目

sql server导出数据字典

转自：https://blog.csdn.net/catshitone/article/details/53994932

【1】代码生成器，在线添加表描述

【2】导出 chm文档工具

【3】保存数据字典成　ｈｔｍｌ

【3.1】打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。

【3.2】执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。

【3.3】最终显示效果

【4】获取带目录的html

【最佳实践】查看数据字典

查看单库字典

查看全库

相关文章

当前栏目

sql server导出数据字典

转自：https://blog.csdn.net/catshitone/article/details/53994932

【1】代码生成器，在线添加表描述

【2】导出 chm文档工具

【3】保存数据字典成 ｈｔｍｌ

【3.1】打开sql server管理器，给选定的表添加描述信息，给指定的字段添加描述信息。

【3.2】执行以下这段sql语句，然后复制查询到的结果到文本，保存为html即可。

【3.3】最终显示效果

【4】获取带目录的html

【最佳实践】查看数据字典

查看单库字典

查看全库

相关文章

【3】保存数据字典成　ｈｔｍｌ