SqlServer表结构查询

一、前言

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

  SqlServer表结构查询

  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

  SqlServer表结构查询

三、参考链接

  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 返回的信息
上一篇:html5 自定义标签取值


下一篇:ViewPager禁止滑动以及它与内层滑动控件水平方向上事件冲突的解决方法