解释 SQL Server 中 sys.objects 中的类型代码

问题描述

在 SQL Server 上，sys.objects 表包括Type"和Type_Desc"属性.例如，对于我的一个数据库:

SELECT DISTINCT [Type], Type_Desc从系统对象按 [类型] 订购

返回:

<前>C CHECK_CONSTRAINTD DEFAULT_CONSTRAINTF FOREIGN_KEY_CONSTRAINTFN SQL_SCALAR_FUNCTIONFS CLR_SCALAR_FUNCTIONIT INTERNAL_TABLEP SQL_STORED_PROCEDUREPK PRIMARY_KEY_CONSTRAINT系统表SQ SERVICE_QUEUETR SQL_TRIGGER用户表昆士兰大学 UNIQUE_CONSTRAINT视图

不同的 DB 有不同的结果，具体取决于使用的类型.

某处是否有这些类型的完整列表?对 sys.objects 没有约束指向这些表，并且 sys.types 包含数据类型.我搜索过 SQL BOL 但没有找到.任何帮助，将不胜感激.

一些数据库只使用这些类型的一个子集.例如，如果我有一个没有视图的数据库，当我按上述方式查询 Sys.Objects 时，结果中没有V"行.我正在寻找 SQL Server 使用的所有可能类型和描述的列表.

BOL 有一个完整的列表，但你不能加入.

AF = 聚合函数 (CLR)C = 检查约束D = DEFAULT(约束或独立)F = 外键约束FN = SQL 标量函数FS = 汇编 (CLR) 标量函数FT = 汇编 (CLR) 表值函数IF = SQL 内联表值函数IT = 内表P = SQL 存储过程PC = 汇编 (CLR) 存储过程PG = 计划指南PK = PRIMARY KEY 约束R = 规则(旧式，独立)RF = 复制过滤程序S = 系统基表SN = 同义词SQ = 服务队列TA = 汇编 (CLR) DML 触发器TF = SQL 表值函数TR = SQL DML 触发器TT = 表格类型U = 表(用户定义)UQ = 唯一约束V = 视图X = 扩展存储过程

转到此信息的最佳 SQL Server 源:sys.objects (Transact-SQL) 它没有提到要加入的任何表.我不能说我曾经注意到任何系统表或视图都可以连接到代码表.

我猜您必须创建自己的表或视图，或者只使用 sys.objects 中的 Type_Desc 属性.

On SQL Server, the sys.objects table includes "Type" and "Type_Desc" attributes. For example, for one of my DBs:

SELECT DISTINCT [Type], Type_Desc
FROM Sys.Objects
ORDER BY [Type]

Returns:

C       CHECK_CONSTRAINT  
D       DEFAULT_CONSTRAINT  
F       FOREIGN_KEY_CONSTRAINT  
FN      SQL_SCALAR_FUNCTION  
FS      CLR_SCALAR_FUNCTION  
IT      INTERNAL_TABLE  
P       SQL_STORED_PROCEDURE  
PK      PRIMARY_KEY_CONSTRAINT  
S       SYSTEM_TABLE  
SQ      SERVICE_QUEUE  
TR      SQL_TRIGGER  
U       USER_TABLE  
UQ      UNIQUE_CONSTRAINT  
V       VIEW  

Different DBs have different results, depending on what types are used.

Is there a comprehensive list of these types somewhere? There isn't a constraint on sys.objects that points me to table of these, and sys.types contains data types. I've searched SQL BOL but haven't found it. Any help would be appreciated.

EDIT: Some DBs use only a subset of these types. For example, if I have a database with no views, when I query Sys.Objects as above, there are no "V" rows in the results. I am looking for a list of all possible types and descriptions used by SQL Server.

BOL has a complete list, but you can't join on that.

AF = Aggregate function (CLR)
C  = CHECK constraint
D  = DEFAULT (constraint or stand-alone)
F  = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P  = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R  = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S  = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U  = Table (user-defined)
UQ = UNIQUE constraint
V  = View
X  = Extended stored procedure

Going to the best SQL Server source for this info: sys.objects (Transact-SQL) it doesn't mention any table to join to. I can't say that I have ever noticed a codes table to join to for any of the systems tables or views.

I guess you'll have to create your own table or view, or just use the Type_Desc attribute in sys.objects.

这篇关于解释 SQL Server 中 sys.objects 中的类型代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！