sysobjects syscolumns和SysTypes笔记

最新推荐文章于 2021-11-23 17:07:18 发布

转载最新推荐文章于 2021-11-23 17:07:18 发布 · 862 阅读

3 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/WuLex

SqlServer数据库专栏收录该内容

196 篇文章

订阅专栏

本文深入探讨了SQL Server系统表sysobjects、syscolumns和systypes的功能与使用方法，包括如何查询表信息、列信息及数据类型。揭示了type与xtype字段之间的联系，以及在高版本SQL Server中使用这些系统表时的注意事项。

1.`sysobjects`

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

常规用法：select * from sysobjects where id= object_id('TableName') and type = 'U' 查找表TableName的信息

更多参考http://msdn.microsoft.com/zh-cn/library/ms177596.aspx

2.`syscolumns`

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

常规用法：select * from syscolumns where id = object_id('TableName') 查找表TableName的列信息

更多参考http://msdn.microsoft.com/zh-cn/library/ms186816.aspx

3.`systypes`

为数据库中定义的每种系统提供的数据类型和每种用户定义的数据类型返回一行。

常规用法：select * from SysTypes where xtype = 167 查找某个列的数据类型

更多参考http://msdn.microsoft.com/zh-cn/library/ms175109.aspx

列名	数据类型	描述
name	sysname	数据类型名称。
xtype	tinyint	物理存储类型。
status	tinyint	标识为仅供参考。不提供支持。不保证以后的兼容性。
xusertype	smallint	扩展用户类型。如果数据类型的数字超过 32,767，则溢出或返回 NULL。
length	smallint	数据类型的物理长度。
xprec	tinyint	服务器使用的内部精度。不在查询中使用。
xscale	tinyint	服务器使用的内部小数位数。不在查询中使用。
tdefault	int	特定存储过程的 ID，此存储过程包含对该数据类型的完整性检查功能。
域	int	特定存储过程的 ID，此存储过程包含对该数据类型的完整性检查功能。
uid	smallint	所有者类型的架构 ID。
		对于从旧版 SQL Server 升级的数据库，架构 ID 等于所有者的用户 ID。
		** 重要* * 如果您使用任何以下SQL ServerDDL 语句，则必须使用sys.types目录视图，而不是sys.systypes。
		`ALTER AUTHORIZATION ON TYPE`
		`CREATE TYPE`
		如果用户数和角色数超过 32,767，则发生溢出或返回 NULL。
保留	smallint	标识为仅供参考。不提供支持。不保证以后的兼容性。
collationid	int	如果基于字符， `collationid`是当前数据库中; 的排序规则的 id，否则，则为 NULL。
usertype	smallint	用户类型 ID。如果数据类型的数字超过 32,767，则溢出或返回 NULL。
variable	bit	可变长度数据类型。
		1 = True
		0 = False
allownulls	bit	指示此数据类型的默认为空性。如果为 null 性指定通过使用此默认值通过CREATE TABLE或ALTER TABLE。
type	tinyint	物理存储数据类型。
printfmt	varchar(255)	标识为仅供参考。不提供支持。不保证以后的兼容性。
prec	smallint	此数据类型的精度级别。
		-1 = xml或大值类型。
scale	tinyint	此数据类型根据精度确定的小数位数。
		NULL = 数据类型不是数值。
排序规则	sysname	如果基于字符，排序规则是排序规则的当前数据库中; 否则为，则为 NULL。

对于用户数和角色数超过32767，用上面3个表可能会造成溢出或返回null，为了避免可以参考http://msdn.microsoft.com/zh-cn/library/ms187376.aspx

`sql server`:关于系统表`[syscolumns]`和`[systypes]`xtype和type

上网看了一些资料，结合我自己的猜想结论有4点。前三点参照其它的人说法，具体内容，可以在附录里面找到。

1、一种说法是

type是6.0就有的，xtype是7.0才有的。

2、同时保留这两个是为了兼容性考虑。

3、xtype是物理存储类型， type是物理存储数据类型

（参见附录第1个链接）

4、结合上面几点上我以前编程和研究暗黑数据库的经验，也许xtype和type之间还有一种递归或者继承的关系在里面。

在附录1的页面上有人做了一个实验：

select name,xtype,type,status from systypes where type=39

结果是得到了’nvarchar'等4个类型

我又做了一个实验：

select name,xtype,type,status from systypes ORDER BY [type]

结果中我看到这样的内容：

ntext 99 35 0
text 35 35 0

这表明type和xtype之间肯定有关某种关联。看起来不太像暗黑的递归，倒类似面向对象里面的继承。可是在表systypes里面，可以看到不管是xtype还是type都不是连续的数字。那么缺少的那些到哪里去了呢？在这个地方我有一个待验证的想法：是不是还有另外一个表，存储了type=39中的更多数据呢？