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

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 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 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 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 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.



GET = 外部資料表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,並OBJECTPROPERTY() 內建函數,在 sys.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:

PermissionsPermissions

目錄檢視內中繼資料的可見性會限制在使用者所擁有的安全性實體,或已授與使用者某些權限的安全性實體。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 (-SQL&#41;) 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 (-SQL&#41;)sys.internal_tables (Transact-SQL)