图片 4

sqlserver数据库中的表、字段sql语句

1.系统表sysobjects

一、前言

在数据库中创建的每个对象都对应一行。

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

列名

  图片 1

数据类型

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

说明

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

name

 

sysname

二、脚本及查询示例

对象名

图片 2图片 3

id

 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 

int

View Code

对象标识号

 

xtype

  图片 4

char(2)

 

对象类型。可以是以下对象类型之一:

三、参考链接

AF = 聚合函数 (CLR)

  

C = CHECK 约束

 

D = 默认值或 DEFAULT 约束

四、脚本解读

F = FOREIGN KEY 约束

syscolumns

L = 日志

为每个表和视图中的每列返回一行,并为数据库中的存储过程的每个参数返回一行。

FN = 标量函数

name

FS = 程序集 (CLR) 标量函数

列名或过程参数的名称。

FT = 程序集 (CLR) 表值函数

id

IF = 内联表函数

此列所属表的对象 ID,或者与此参数关联的存储过程的 ID

IT = 内部表

xusertype

P = 存储过程

扩展的用户定义数据类型的 ID

PC = 程序集 (CLR) 存储过程

colid

PK = PRIMARY KEY 约束

列 ID 或参数 ID

RF = 复制筛选存储过程

 

S = 系统表

 

SN = 同义词

 

SQ = 服务队列

 

TA = 程序集 (CLR) DML 触发器

 

TF = 表函数

systypes

TR = SQL DML 触发器

对于每种系统提供数据类型和用户定义数据类型,均包含一行信息。

TT = 表类型

name

U = 用户表

数据类型名称

UQ = UNIQUE 约束

xusertype

V = 视图

扩展用户类型

X = 扩展存储过程

 

uid

 

smallint

 

对象所有者的架构 ID。

sysobjects

对于从旧版 SQL Server 升级的数据库,架构 ID 等于所有者的用户 ID。

在数据库中创建的每个对象(例如约束、默认值、日志、规则以及存储过程)都对应一行

重要提示:如果使用以下任何一个 SQL Server DDL 语句,则必须使用
sys.objects 目录视图而不是 sys.sysobjects。 CREATE | ALTER | DROP USER
CREATE | ALTER | DROP ROLE CREATE | ALTER | DROP APPLICATION ROLE CREATE
SCHEMA ALTER AUTHORIZATION ON OBJECT

id

如果用户数和角色数超过 32,767,则发生溢出或返回 NULL。

 对象标识号

有关详细信息,请参阅查询 SQL Server 系统目录。

xtype

info

 对象类型。 可以是以下对象类型之一: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 = 扩展存储过程

smallint

name

标识为仅供参考。不提供支持。不保证以后的兼容性。

对象名(dtproperties自动生成表此处不讨论)

status

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

base_schema_ver

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

replinfo

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

parent_obj

 

int

 

父对象的对象标识号。例如,表 ID。

 

crdate

 

datetime

 

对象的创建日期。

 

ftcatid

 

smallint

 

注册为使用全文索引的所有用户表的全文目录标识符,对于没有注册的所有用户表则为
0。

 

schema_ver

 

int

 

在每次更改表的架构时都会增加的版本号。始终返回 0。

 

stats_schema_ver

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

type

 

char(2)

 

对象类型。可以是下列值之一:

 

AF = 聚合函数 (CLR)

 

C = CHECK 约束

sys.extended_properties

D = 默认值或 DEFAULT 约束

针对当前数据库中的每个扩展属性返回一行

F = FOREIGN KEY 约束

class

FN = 标量函数

标识其上存在属性的项类。 可以是下列值之一:

FS = 程序集 (CLR) 标量函数

0 = 数据库

FT = 程序集 (CLR) 表值函数 IF = 内联表函数

1 = 对象或列

IT – 内部表

2 = 参数

K = PRIMARY KEY 或 UNIQUE 约束

3 = 架构

L = 日志

4 = 数据库主体

P = 存储过程

5 = 程序集

PC = 程序集 (CLR) 存储过程

6 = 类型

R = 规则

7 = 索引

RF = 复制筛选存储过程

10 =

S = 系统表

15 = 消息类型

SN = 同义词

16 = 服务约定

SQ = 服务队列

17 = 服务

TA = 程序集 (CLR) DML 触发器

18 = 远程服务绑定

TF = 表函数

19 = 路由

TR = SQL DML 触发器

20 = 数据空间(文件组或分区方案)

TT = 表类型

21 = 分区函数

U = 用户表

22 = 数据库文件

V = 视图

27 = 计划指南

X = 扩展存储过程

major_id

userstat

其上存在扩展属性的项的 ID,根据项类进行解释。 对于大多数项,该 ID
适用于类所表示的项。 下列是非标准主 ID 的解释:

smallint

如果 class 为 0,则 major_id 始终为 0。

标识为仅供参考。不提供支持。不保证以后的兼容性。

如果 class 为 1、2 或 7,则 major_id 为 object_id。

sysstat

minor_id

smallint

其上存在扩展属性的项的辅助 ID,根据项类进行解释。 对于大多数项,ID 为
0;否则,ID 为下列值之一:

标识为仅供参考。不提供支持。不保证以后的兼容性。

如果 class = 1,则 minor_id 在项为列的情况下等于
column_id,在项为对象的情况下等于 0。

indexdel

如果 class = 2,则 minor_id 为 parameter_id。

smallint

如果 class = 7,则 minor_id 为 index_id。

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

refdate

 

datetime

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

version

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

deltrig

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

instrig

 

int

 

标识为仅供参考。不提供支持。不保证以后的兼容性。

 

updtrig

 

int