SqlServer表结构查询

一、前言

　　近两天项目升级数据迁移，将老版本（sqlserver）的数据迁移到新版本（mysql）数据库，需要整理一个Excel表格出来，映射两个库之间的表格字段，示例如下：

　　

　　Mysql数据库查询表结构很方便，用客户端（SQLyog）可以直接复制出需要的表结构，据说可以用简单的命令查询表结构，但是不会...

　　Sqlserver导出表结构就很坑爹了，首先呢客户端不支持，表设计页面虽说可以复制表结构但是不包含字段注释，想想这种场景应该很常见不可能没有解决方案的，于是在网上发现了一段脚本，稍加调整就可以查询出需要的表结构

二、脚本及查询示例

SELECT  表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END ,
        字段说明 = ISNULL(g.[value], '') ,
        字段名 = a.name ,
        类型 = CASE WHEN b.name IN ( 'varchar', 'nvarchar' )
                  THEN b.name + '('
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
                       + ')'
                  WHEN b.name = 'decimal'
                  THEN b.name + '('
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))
                       + ','
                       + CAST(COLUMNPROPERTY(a.id, a.name, 'Scale') AS VARCHAR(4))
                       + ')'
                  ELSE b.name
             END
FROM syscolumns a    -- 列名
    LEFT JOIN systypes b ON a.xusertype = 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    --扩展属性(字段说明)
    --LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0        --扩展属性(表说明)
WHERE d.name = 'Merchant'    --可修改表名
ORDER BY a.id , a.colorder  

　　

三、参考链接

　　http://blog.csdn.net/shandian534/article/details/39249245

四、脚本解读

syscolumns
为每个表和视图中的每列返回一行，并为数据库中的存储过程的每个参数返回一行。
name	列名或过程参数的名称。
id	此列所属表的对象 ID，或者与此参数关联的存储过程的 ID
xusertype	扩展的用户定义数据类型的 ID
colid	列 ID 或参数 ID

 

systypes
对于每种系统提供数据类型和用户定义数据类型，均包含一行信息。
name	数据类型名称
xusertype	扩展用户类型

sysobjects
在数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）都对应一行
id	对象标识号
xtype	对象类型。 可以是以下对象类型之一： AF = 聚合函数 (CLR) C = CHECK 约束 D = 默认值或 DEFAULT 约束 F = FOREIGN KEY 约束 L = 日志 FN = 标量函数 FS = 程序集 (CLR) 标量函数 FT = 程序集 (CLR) 表值函数 IF = 内联表函数 IT = 内部表 P = 存储过程 PC = 程序集 (CLR) 存储过程 PK = PRIMARY KEY 约束（type 为 K） RF = 复制筛选存储过程 S = 系统表 SN = 同义词 SQ = 服务队列 TA = 程序集 (CLR) DML 触发器 TF = 表函数 TR = SQL DML 触发器 TT = 表类型 U = 用户表 UQ = UNIQUE 约束（type 为 K） V = 视图 X = 扩展存储过程
name	对象名（dtproperties自动生成表此处不讨论）

sys.extended_properties
针对当前数据库中的每个扩展属性返回一行
class	标识其上存在属性的项类。 可以是下列值之一： 0 = 数据库 1 = 对象或列 2 = 参数 3 = 架构 4 = 数据库主体 5 = 程序集 6 = 类型 7 = 索引 10 = XML 架构集合 15 = 消息类型 16 = 服务约定 17 = 服务 18 = 远程服务绑定 19 = 路由 20 = 数据空间（文件组或分区方案） 21 = 分区函数 22 = 数据库文件 27 = 计划指南
major_id	其上存在扩展属性的项的 ID，根据项类进行解释。 对于大多数项，该 ID 适用于类所表示的项。 下列是非标准主 ID 的解释： 如果 class 为 0，则 major_id 始终为 0。 如果 class 为 1、2 或 7，则 major_id 为 object_id。
minor_id	其上存在扩展属性的项的辅助 ID，根据项类进行解释。 对于大多数项，ID 为 0；否则，ID 为下列值之一： 如果 class = 1，则 minor_id 在项为列的情况下等于 column_id，在项为对象的情况下等于 0。 如果 class = 2，则 minor_id 为 parameter_id。 如果 class = 7，则 minor_id 为 index_id。

COLUMNPROPERTY
返回有关列或参数的信息
参数：
id	一个表达式，其中包含表或过程的标识符 (ID)
column	一个表达式，其中包含列或参数的名称
property	一个表达式，其中包含要为 id 返回的信息