sys.objects (Transact-SQL)sys.objects (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

为每个数据库,包括本机编译标量用户定义函数中创建的用户定义的架构范围内对象对应一行。Contains a row for each user-defined, schema-scoped object that is created within a database, including natively compiled scalar user-defined function.

有关详细信息,请参阅内存中 OLTP 的标量用户定义函数For more information, see Scalar User-Defined Functions for In-Memory OLTP.

备注

sys.objects 不显示 DDL 触发器,因为它们不是架构范围内的对象。sys.objects does not show DDL triggers, because they are not schema-scoped. 所有触发器,DML 和 DDL,都位于sys.triggersAll triggers, both DML and DDL, are found in sys.triggers. sys.triggers 支持对各种触发器应用混合名称范围规则。sys.triggers supports a mixture of name-scoping rules for the various kinds of triggers.

列名Column name 数据类型Data type 描述Description
namename sysnamesysname 对象名。Object name.
object_idobject_id intint 对象标识号。Object identification number. 是在数据库中唯一。Is unique within a database.
principal_idprincipal_id intint 如果不是架构所有者,则为单个所有者的 ID。ID of the individual owner, if different from the schema owner. 默认情况下,架构包含的对象由架构所有者拥有。By default, schema-contained objects are owned by the schema owner. 不过,通过使用 ALTER AUTHORIZATION 语句更改所有权可以指定备用所有者。However, an alternate owner can be specified by using the ALTER AUTHORIZATION statement to change ownership.

如果没有备用的单个所有者,则为 NULL。Is NULL if there is no alternate individual owner.

如果对象类型为下列类型之一,则为 NULL:Is NULL if the object type is one of the following:

C = CHECK 约束C = CHECK constraint

D = DEFAULT(约束或独立)D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY 约束F = FOREIGN KEY constraint

PK = PRIMARY KEY 约束PK = PRIMARY KEY constraint

R = 规则(旧式,独立)R = Rule (old-style, stand-alone)

TA = 程序集(CLR 集成)触发器TA = Assembly (CLR-integration) trigger

TR = SQL 触发器TR = SQL trigger

UQ = UNIQUE 约束UQ = UNIQUE constraint

EC = 边缘约束EC = Edge constraint
schema_idschema_id intint 包含该对象的架构的 ID。ID of the schema that the object is contained in.

始终包含在 sys 或 INFORMATION_SCHEMA 架构中的架构范围内的系统对象。Schema-scoped system objects are always contained in the sys or INFORMATION_SCHEMA schemas.
parent_object_idparent_object_id intint 此对象所属对象的 ID。ID of the object to which this object belongs.

0 = 不是子对象。0 = Not a child object.
typetype char(2)char(2) 对象类型:Object type:

AF = 聚合函数 (CLR)AF = Aggregate function (CLR)

C = CHECK 约束C = CHECK constraint

D = DEFAULT(约束或独立)D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY 约束F = FOREIGN KEY constraint

FN = SQL 标量函数FN = SQL scalar function

FS = 程序集 (CLR) 标量函数FS = Assembly (CLR) scalar-function

FT = 程序集 (CLR) 表值函数FT = Assembly (CLR) table-valued function

IF = SQL 内联表值函数IF = SQL inline table-valued function

IT = 内部表IT = Internal table

P = SQL 存储过程P = SQL Stored Procedure

PC = 程序集 (CLR) 存储过程PC = Assembly (CLR) stored-procedure

PG = 计划指南PG = Plan guide

PK = PRIMARY KEY 约束PK = PRIMARY KEY constraint

R = 规则(旧式,独立)R = Rule (old-style, stand-alone)

RF = 复制筛选过程RF = Replication-filter-procedure

S = 系统基表S = System base table

SN = 同义词SN = Synonym

SO = 序列对象SO = Sequence object

U = 表(用户定义类型)U = Table (user-defined)

V = 视图V = View

EC = 边缘约束EC = Edge constraint



适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.



SQ = 服务队列SQ = Service queue

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

TF = SQL 表值函数TF = SQL table-valued-function

TR = SQL DML 触发器TR = SQL DML trigger

TT = 表类型TT = Table type

UQ = UNIQUE 约束UQ = UNIQUE constraint

X = 扩展存储过程X = Extended stored procedure



适用于:SQL Server 2016 (13.x)SQL Server 2016 (13.x)通过SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseAzure SQL 数据仓库Azure SQL Data Warehouse并行数据仓库Parallel Data WarehouseApplies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x) through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database, Azure SQL 数据仓库Azure SQL Data Warehouse, 并行数据仓库Parallel Data Warehouse.



ET = 外部表ET = External Table
type_desctype_desc nvarchar(60)nvarchar(60) 对对象类型的说明:Description of the object type:

AGGREGATE_FUNCTIONAGGREGATE_FUNCTION

CHECK_CONSTRAINTCHECK_CONSTRAINT

CLR_SCALAR_FUNCTIONCLR_SCALAR_FUNCTION

CLR_STORED_PROCEDURECLR_STORED_PROCEDURE

CLR_TABLE_VALUED_FUNCTIONCLR_TABLE_VALUED_FUNCTION

CLR_TRIGGERCLR_TRIGGER

DEFAULT_CONSTRAINTDEFAULT_CONSTRAINT

EXTENDED_STORED_PROCEDUREEXTENDED_STORED_PROCEDURE

FOREIGN_KEY_CONSTRAINTFOREIGN_KEY_CONSTRAINT

INTERNAL_TABLEINTERNAL_TABLE

PLAN_GUIDEPLAN_GUIDE

PRIMARY_KEY_CONSTRAINTPRIMARY_KEY_CONSTRAINT

REPLICATION_FILTER_PROCEDUREREPLICATION_FILTER_PROCEDURE

RULERULE

SEQUENCE_OBJECTSEQUENCE_OBJECT



适用范围SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL Server 2017SQL Server 2017Applies to: SQL Server 2012 (11.x)SQL Server 2012 (11.x) through SQL Server 2017SQL Server 2017.



SERVICE_QUEUESERVICE_QUEUE

SQL_INLINE_TABLE_VALUED_FUNCTIONSQL_INLINE_TABLE_VALUED_FUNCTION

SQL_SCALAR_FUNCTIONSQL_SCALAR_FUNCTION

SQL_STORED_PROCEDURESQL_STORED_PROCEDURE

SQL_TABLE_VALUED_FUNCTIONSQL_TABLE_VALUED_FUNCTION

SQL_TRIGGERSQL_TRIGGER

SYNONYMSYNONYM

SYSTEM_TABLESYSTEM_TABLE

TABLE_TYPETABLE_TYPE

UNIQUE_CONSTRAINTUNIQUE_CONSTRAINT

USER_TABLEUSER_TABLE

VIEWVIEW
create_datecreate_date datetimedatetime 对象的创建日期。Date the object was created.
modify_datemodify_date datetimedatetime 上次使用 ALTER 语句修改对象的日期。Date the object was last modified by using an ALTER statement. 如果对象为表或视图,则创建或修改表或视图的聚集索引时,modify_date 也会随之更改。If the object is a table or a view, modify_date also changes when a clustered index on the table or view is created or altered.
is_ms_shippedis_ms_shipped bitbit 对象由内部 SQL ServerSQL Server 组件创建。Object is created by an internal SQL ServerSQL Server component.
is_publishedis_published bitbit 对象为发布对象。Object is published.
is_schema_publishedis_schema_published bitbit 仅发布对象的架构。Only the schema of the object is published.

备注Remarks

您可以将应用OBJECT_IDOBJECT_NAME,并OBJECTPROPERTYsys.objects 中所示的对象 () 内置函数。You can apply the OBJECT_ID, OBJECT_NAME, and OBJECTPROPERTY() built-in functions to the objects shown in sys.objects.

此视图与相同的架构,名为版本sys.system_objects,显示系统对象。There is a version of this view with the same schema, called sys.system_objects, that shows system objects. 没有名为的另一个视图sys.all_objects显示系统和用户对象。There is another view called sys.all_objects that shows both system and user objects. 所有这三个目录视图的结构都相同。All three catalog views have the same structure.

在此版本的 SQL ServerSQL Server 中,扩展索引(例如 XML 索引或空间索引)将视为 sys.objects 中的内部表(type = IT,type_desc = INTERNAL_TABLE)。In this version of SQL ServerSQL Server, an extended index, such as an XML index or spatial index, is considered an internal table in sys.objects (type = IT and type_desc = INTERNAL_TABLE). 对于扩展索引:For an extended index:

  • name 是索引表的内部名称。name is the internal name of the index table.

  • parent_object_id 是基表的 object_id。parent_object_id is the object_id of the base table.

  • is_ms_shipped、is_published 和 is_schema_published 列设置为 0。is_ms_shipped, is_published and is_schema_published columns are set to 0.

相关的有用系统视图Related useful system views
可以通过使用系统视图为某个特定类型的对象,如查看对象的子集:Subsets of the objects can be viewed by using system views for a specific type of object, such as:

权限Permissions

目录视图中仅显示用户拥有的安全对象的元数据,或用户对其拥有某些权限的安全对象的元数据。The visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. 有关详细信息,请参阅 Metadata Visibility ConfigurationFor more information, see Metadata Visibility Configuration.

示例Examples

A.A. 返回在最近 N 天内修改过的所有对象Returning all the objects that have been modified in the last N days

运行以下查询之前,请使用有效值替换 <database_name><n_days>Before you run the following query, replace <database_name> and <n_days> with valid values.

USE <database_name>;  
GO  
SELECT name AS object_name   
  ,SCHEMA_NAME(schema_id) AS schema_name  
  ,type_desc  
  ,create_date  
  ,modify_date  
FROM sys.objects  
WHERE modify_date > GETDATE() - <n_days>  
ORDER BY modify_date;  
GO  

B.B. 返回指定存储过程或函数的参数Returning the parameters for a specified stored procedure or function

运行以下查询之前,请使用有效名称替换 <database_name><schema_name.object_name>Before you run the following query, replace <database_name> and <schema_name.object_name> with valid names.

USE <database_name>;  
GO  
SELECT SCHEMA_NAME(schema_id) AS schema_name  
    ,o.name AS object_name  
    ,o.type_desc  
    ,p.parameter_id  
    ,p.name AS parameter_name  
    ,TYPE_NAME(p.user_type_id) AS parameter_type  
    ,p.max_length  
    ,p.precision  
    ,p.scale  
    ,p.is_output  
FROM sys.objects AS o  
INNER JOIN sys.parameters AS p ON o.object_id = p.object_id  
WHERE o.object_id = OBJECT_ID('<schema_name.object_name>')  
ORDER BY schema_name, object_name, p.parameter_id;  
GO  

C.C. 返回数据库中的所有用户定义函数Returning all the user-defined functions in a database

运行以下查询之前,请使用有效数据库名称替换 <database_name>Before you run the following query, replace <database_name> with a valid database name.

USE <database_name>;  
GO  
SELECT name AS function_name   
  ,SCHEMA_NAME(schema_id) AS schema_name  
  ,type_desc  
  ,create_date  
  ,modify_date  
FROM sys.objects  
WHERE type_desc LIKE '%FUNCTION%';  
GO  

D.D. 返回架构中每个对象的所有者。Returning the owner of each object in a schema.

运行以下查询之前,请使用有效名称替换所有的 <database_name><schema_name>Before you run the following query, replace all occurrences of <database_name> and <schema_name> with valid names.

USE <database_name>;  
GO  
SELECT 'OBJECT' AS entity_type  
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name  
    ,name   
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'  
UNION   
SELECT 'TYPE' AS entity_type  
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name  
    ,name   
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>'   
UNION  
SELECT 'XML SCHEMA COLLECTION' AS entity_type   
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name  
    ,xsc.name   
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s  
    ON s.schema_id = xsc.schema_id  
WHERE s.name = '<schema_name>';  
GO  

请参阅See Also

目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
sys.all_objects (TRANSACT-SQL) sys.all_objects (Transact-SQL)
sys.system_objects (Transact-SQL) sys.system_objects (Transact-SQL)
sys.triggers (Transact-SQL) sys.triggers (Transact-SQL)
对象目录视图 (Transact-SQL) Object Catalog Views (Transact-SQL)
查询 SQL Server 系统目录常见问题 Querying the SQL Server System Catalog FAQ
sys.internal_tables (TRANSACT-SQL)sys.internal_tables (Transact-SQL)