欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

获取sql server 指定数据库指定表的基本信息

程序员文章站 2022-05-06 11:53:15
...

/*============================================================================== * *Filename:GetTableInfor.sql *Description:获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期 开发代码生成器做准备。tbname可以换成自己需

/*==============================================================================
*
* Filename: GetTableInfor.sql
* Description: 获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期
开发代码生成器做准备。tbname可以换成自己需要查询的表名称 即可
* Version: 1.0
* Created: 2012.08.02
* Author: liangjw
* E-mail : liangjw0504@163.com
* Q Q : 592568532
* Profile Url:http://90ideas.net/
* Company: Copyright (C) Create Family Wealth Power By Peter
*
==============================================================================*/
* 备注信息: 上传部分自己总结的常用方法的封装,有不足和不完美之处,希望大家指出来,愿意一起
* 主要研究erp,cms,crm,b2b,oa等系统和网站的开发,欢迎有共同追求和学的IT人员一起学习和交流。
* 学习和讨论有关asp.net mvc ,Ajax ,jquery ,html/css, xml ,sqlserver ,wpf,IIS以及服务器的搭建和安全性相关技术的交流和学习。 获取sql server 指定数据库指定表的基本信息
/*==============================================================================
*
* Filename: GetTableInfor.sql
* Description: 获取指定数据库指定表的字段以及字段长度,字段说明等相关数据信息,便于后期
 开发代码生成器做准备。tbname可以换成自己需要查询的表名称 即可
* Version: 1.0
* Created: 2012.08.02
* Author: Your name
* Company: Copyright (C) Create Family Wealth Power By Peter
*
==============================================================================*/
SELECT
    colorder = C.column_id ,
    ColumnName = C.name ,
    TypeName = T.name ,
    Length = CASE WHEN T.name = 'nchar' THEN C.max_length / 2
                  WHEN T.name = 'nvarchar' THEN C.max_length / 2
                  ELSE C.max_length
             END ,
    Preci = C.precision ,
    Scale = C.scale ,
    IsIdentity = CASE WHEN C.is_identity = 1 THEN N'1'
                      ELSE N''
                 END ,
    isPK = ISNULL(IDX.PrimaryKey, N'') ,
    Computed = CASE WHEN C.is_computed = 1 THEN N'1'
                    ELSE N''
               END ,
    IndexName = ISNULL(IDX.IndexName, N'') ,
    IndexSort = ISNULL(IDX.Sort, N'') ,
    Create_Date = O.Create_Date ,
    Modify_Date = O.Modify_date ,
    cisNull = CASE WHEN C.is_nullable = 1 THEN N'1'
                   ELSE N''
              END ,
    defaultVal = ISNULL(D.definition, N'') ,
    deText = ISNULL(PFD.[value], N'')
FROM
    sys.columns C
    INNER JOIN sys.objects O ON C.[object_id] = O.[object_id]
                                AND ( O.type = 'U'
                                      OR O.type = 'V'
                                    )
                                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
    LEFT JOIN sys.extended_properties PTB ON PTB.class = 1
                                             AND PTB.minor_id = 0
                                             AND C.[object_id] = PTB.major_id
    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'1'
                          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 = N'tbname'  --数据库中表的名称tbname可以替换成自己需要查询的表
ORDER BY
    O.name ,
    C.column_id