你的位置:首页 > 数据库

[数据库]SqlServer表结构查询


一、前言

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

  

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

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

 

二、脚本及查询示例

 1 SELECT 表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END , 2     字段说明 = ISNULL(g.[value], '') , 3     字段名 = a.name , 4     类型 = CASE WHEN b.name IN ( 'varchar', 'nvarchar' ) 5          THEN b.name + '(' 6            + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4)) 7            + ')' 8          WHEN b.name = 'decimal' 9          THEN b.name + '('10            + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS VARCHAR(4))11            + ','12            + CAST(COLUMNPROPERTY(a.id, a.name, 'Scale') AS VARCHAR(4))13            + ')'14          ELSE b.name15       END16 FROM syscolumns a  -- 列名17   LEFT JOIN systypes b ON a.xusertype = b.xusertype  -- 类型18   INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'  --筛选用户对象19   --LEFT JOIN syscomments e ON a.cdefault = e.id  --默认值20   LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id  --扩展属性(字段说明)21   --LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0    --扩展属性(表说明)22 WHERE d.name = 'Merchant'  --可修改表名23 ORDER BY a.id , a.colorder 

View Code

 

  

 

三、参考链接

  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 =

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 返回的信息