首页 > 数据库 >Sqlserver 导出表数据类型和字段详细信息

Sqlserver 导出表数据类型和字段详细信息

时间：2022-11-08 14:55:06浏览次数：48  
标签：VARCHAR 数据类型 object Sqlserver clmns DECLARE PRINT 详细信息 id 

USE [Database Name]

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;

运气之后，改成后缀未".HTML"运行即可

 

标签：VARCHAR,数据类型,object,Sqlserver,clmns,DECLARE,PRINT,详细信息,id
From： https://www.cnblogs.com/duhaoran/p/16869713.html

相关文章

  • JS基础+变量+数据类型
    js概述====≥使用方式js是什么=====》js是运行在浏览器/客户端上的一种编程语言，实现人机交互。js能做什么===》现在几乎是全能=〉数据交互，表单校验，app的开发，游戏开发，网......
  • Long数据类型序列化Json后传递给前端，产生的精度丢失的问题解决
    问题产生的原因Long类型的数据，如果我们在后端将结果序列化为json，直接传给前端的话，在Long长度大于17位时会出现精度丢失的问题。java中的long能表示的范围比js中number大,......
  • Day04.1数据类型拓展
    Java数据类型拓展篇整数拓展二进制的表示：数值前面加0bintnum1=10;//此值表示十进制的10intnum2=0b10;//此值表示二进制的10八进制的表示：数值前面加0intnum3=......
  • Java——基本数据类型
                    注意： 整型（int）常量默认在内存中占32位，是具有整数类型的值，当运算过程中所需值超过32位长度时，可以把它表示为长整......
  • 二.adb 查看camera 详细信息 camera配置，先不做兼容
     cat/proc/drivers/imgsensor查看手机分辨率adbshellwmsize 2.1.adbshelldumpsysmedia.camera   availableJpegSizes 关机adbshellreboot-p ......
  • Day04：Java数据类型
    Java的数据类型强类型语言要求变量的使用要严格符合规定，所有变量都必须先定义后才能使用弱类型语言什么是变量变量：可以变化的量。在Java中每个变量都必须先申明这......
  • MySQL_总结_数据类型
    一数值型1整型Tinyint、smallint、mediumint、int/integer、bigint（根据所占的字节的大小越来越大1、2、3、4、8）特点①    都可以设置无符号和有符号，默认有符......
  • MySQL_数据类型_字符型
    较短文本charvarchar字符串类型最多字符数描述及存储需求char(M)MM:0~255之间的整数varchar(M)MM:0~65535之间的整数区别 写法M的含义......
  • MySQL_数据类型_日期型
    分类Date：只保存日期Time：只保存时间Year：只保存年 Datetime：保存日期+时间Timestamp：保存日期+时间特点 字节范围时区等的影响datetime81000~9999不......
  • MySQL_数据类型_整型
    数据类型字节范围Tinyint1有符号：-128~127无符号：0~255Smallint2有符号：-32768~32767无符号：0~65535Mediumint3有符号：无符号：（不用记）Int、inte......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99