sys.internal_tables (Transact-SQL)sys.internal_tables (Transact-SQL)

適用於: 是SQL Server 否Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

針對每個內部資料表物件,各傳回一個資料列。Returns one row for each object that is an internal table. SQL ServerSQL Server 會自動產生內部資料表來支援各種功能。Internal tables are automatically generated by SQL ServerSQL Server to support various features. 例如,當您建立主要 XML 索引時,SQL ServerSQL Server 就會自動建立內部資料表來保存零碎的 XML 文件資料。For example, when you create a primary XML index, SQL ServerSQL Server automatically creates an internal table to persist the shredded XML document data. 內部資料表會出現在sys結構描述,每個資料庫的唯一系統產生名稱,以便表示其功能,比方說,還有xml_index_nodes_2021582240_32001queue_messages_1977058079Internal tables appear in the sys schema of every database and have unique, system-generated names that indicate their function, for example, xml_index_nodes_2021582240_32001 or queue_messages_1977058079

內部資料表不包含使用者可存取的資料,而且其結構描述是固定且無法變更的。Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. 您無法在 Transact-SQLTransact-SQL 陳述式中參考內部資料表名稱。You cannot reference internal table names in Transact-SQLTransact-SQL statements. 比方說,您無法在其中執行的陳述式,例如 SELECT * FROM <sys.internal_table_name >For example, you cannot execute a statement such as SELECT * FROM <sys.internal_table_name>. 不過,您可以查詢目錄檢視來查看內部資料表的中繼資料。However, you can query catalog views to see the metadata of internal tables.

資料行名稱Column name 資料類型Data type 描述Description
<從 sys.objects 繼承的資料行 ><Columns inherited from sys.objects> 如需這個檢視所繼承的資料行的清單,請參閱 < j (TRANSACT-SQL)For a list of columns that this view inherits, see sys.objects (Transact-SQL).
internal_typeinternal_type tinyinttinyint 內部資料表的類型:Type of the internal table:

3 = query_disk_store_query_hints3 = query_disk_store_query_hints

4 = query_disk_store_query_template_parameterization4 = query_disk_store_query_template_parameterization

6 = query_disk_store_wait_stats6 = query_disk_store_wait_stats

201 = queue_messages201 = queue_messages

202 = xml_index_nodes202 = xml_index_nodes

203 = fulltext_catalog_freelist203 = fulltext_catalog_freelist

205 = query_notification205 = query_notification

206 = service_broker_map206 = service_broker_map

207 = extended_indexes (例如,空間索引)207 = extended_indexes (such as a spatial index)

208 = filestream_tombstone208 = filestream_tombstone

209 = change_tracking209 = change_tracking

210 = tracked_committed_transactions210 = tracked_committed_transactions

220 = contained_features220 = contained_features

225 = filetable_updates225 = filetable_updates

236 = selective_xml_index_node_table236 = selective_xml_index_node_table

240 = query_disk_store_query_text240 = query_disk_store_query_text

241 = query_disk_store_query241 = query_disk_store_query

242 = query_disk_store_plan242 = query_disk_store_plan

243 = query_disk_store_runtime_stats243 = query_disk_store_runtime_stats

244 = query_disk_store_runtime_stats_interval244 = query_disk_store_runtime_stats_interval

245 = query_context_settings245 = query_context_settings
internal_type_descinternal_type_desc nvarchar(60)nvarchar(60) 內部資料表類型的描述:Description of the type of internal table:






















parent_idparent_id intint 父系的識別碼,不論是否以結構描述為範圍,都是如此。ID of the parent, regardless of whether it is schema-scoped or not. 否則,在沒有父系的狀況下,便是 0。Otherwise, 0 if there is no parent.

queue_messages = object_id的佇列queue_messages = object_id of queue

xml_index_nodes = object_id xml 索引xml_index_nodes = object_id of the xml index

fulltext_catalog_freelist = fulltext_catalog_id的全文檢索目錄fulltext_catalog_freelist = fulltext_catalog_id of the full-text catalog

fulltext_index_map = object_id的全文檢索索引fulltext_index_map = object_id of the full-text index

query_notification,或service_broker_map = 0query_notification, or service_broker_map = 0

extended_indexes = object_id的擴充索引,例如,空間索引extended_indexes = object_id of an extended index, such as a spatial index

object_id追蹤已啟用的哪一個資料表的資料表 = change_trackingobject_id of the table for which table tracking is enabled = change_tracking
parent_minor_idparent_minor_id intint 父系的次要識別碼。Minor ID of the parent.

xml_index_nodes = index_id XML 索引xml_index_nodes = index_id of the XML index

extended_indexes = index_id的擴充索引,例如,空間索引extended_indexes = index_id of an extended index, such as a spatial index

0 = queue_messagesfulltext_catalog_freelistfulltext_index_mapquery_notificationservice_broker_map,或change_tracking0 = queue_messages, fulltext_catalog_freelist, fulltext_index_map, query_notification, service_broker_map, or change_tracking
lob_data_space_idlob_data_space_id intint 非零值是存放這份資料表的大型物件 (LOB) 之資料空間 (檔案群組或資料分割結構描述) 的識別碼。Non-zero value is the ID of data space (filegroup or partition-scheme) that holds the large object (LOB) data for this table.
filestream_data_space_idfilestream_data_space_id intint 保留供日後使用。Reserved for future use.


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


內部資料表會與父實體放置於相同的檔案群組中。Internal tables are placed on the same filegroup as the parent entity. 您可以使用下列範例 F 中顯示的目錄查詢,傳回內部資料表針對同資料列、資料列外和大型物件 (LOB) 資料所使用的頁面數。You can use the catalog query shown in Example F below to return the number of pages internal tables consume for in-row, out-of-row, and large object (LOB) data.

您可以使用sp_spaceused系統程序來傳回內部資料表的空間使用狀況資料。You can use the sp_spaceused system procedure to return space usage data for internal tables. sp_spaceused下列方式來報告內部資料表的空間:sp_spaceused reports internal table space in the following ways:

  • 當您指定佇列名稱後,就會參考與此佇列相關聯的基礎內部資料表,並且報告其儲存耗用量。When a queue name is specified, the underlying internal table associated with the queue is referenced and its storage consumption is reported.

  • 中包含的 XML 索引、 空間索引和全文檢索索引之內部資料表所使用的頁面index_size資料行。Pages that are used by the internal tables of XML indexes, spatial indexes, and full-text indexes are included in the index_size column. 指定資料表或索引檢視表名稱時,資料行中包含 XML 索引、 空間索引和全文檢索索引,該物件的頁面保留index_sizeWhen a table or indexed view name is specified, the pages for the XML indexes, spatial indexes, and full-text indexes for that object are included in the columns reserved and index_size.


下列範例將說明如何使用目錄檢視來查詢內部資料表中繼資料。The following examples demonstrate how to query internal table metadata by using catalog views.

A.A. 顯示從 sys.objects 目錄檢視繼承資料行的內部資料表Show internal tables that inherit columns from the sys.objects catalog view

SELECT * FROM sys.objects WHERE type = 'IT';  

B.B. 傳回所有內部資料表中繼資料 (包括從 sys.objects 繼承的資料行)Return all internal table metadata (including that which is inherited from sys.objects)

SELECT * FROM sys.internal_tables;  

C.C. 傳回內部資料表的資料行和資料行資料類型Return internal table columns and column data types

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    , AS internal_table_name  
    , AS column_data_type   
FROM sys.internal_tables AS itab  
JOIN sys.columns AS col ON itab.object_id = col.object_id  
JOIN sys.types AS typ ON typ.user_type_id = col.user_type_id  
ORDER BY, col.column_id;  

D.D. 傳回內部資料表索引Return internal table indexes

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    , AS internal_table_name  
    , idx.*  
FROM sys.internal_tables AS itab  
JOIN sys.indexes AS idx ON itab.object_id = idx.object_id  
ORDER BY, idx.index_id;  

E.E. 傳回內部資料表統計資料Return internal table statistics

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    , AS internal_table_name  
    , s.*  
FROM sys.internal_tables AS itab  
JOIN sys.stats AS s ON itab.object_id = s.object_id  
ORDER BY, s.stats_id;  

F.F. 傳回內部資料表的資料分割和配置單位資訊Return internal table partition and allocation unit information

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    , AS internal_table_name  
    , AS heap_or_index_name  
FROM sys.internal_tables AS itab  
JOIN sys.indexes AS idx  
--     JOIN to the heap or the clustered index  
    ON itab.object_id = idx.object_id AND idx.index_id IN (0,1)  
JOIN   sys.partitions AS p   
    ON p.object_id = idx.object_id AND p.index_id = idx.index_id  
JOIN   sys.allocation_units AS au  
--     IN_ROW_DATA (type 1) and ROW_OVERFLOW_DATA (type 3) => JOIN to partition's Hobt  
--     else LOB_DATA (type 2) => JOIN to the partition ID itself.  
ON au.container_id =    
    CASE au.type   
        WHEN 2 THEN p.partition_id   
        ELSE p.hobt_id   
ORDER BY, idx.index_id;  

G.G. 傳回 XML 索引的內部資料表中繼資料Return internal table metadata for XML indexes

SELECT AS parent_table  
    ,t.object_id AS parent_table_id  
    , AS internal_table_name  
    ,it.object_id AS internal_table_id  
    , AS primary_XML_index_name  
    ,xi.index_id as primary_XML_index_id  
FROM sys.internal_tables AS it  
JOIN sys.tables AS t   
    ON it.parent_id = t.object_id  
JOIN sys.xml_indexes AS xi   
    ON it.parent_id = xi.object_id  
    AND it.parent_minor_id  = xi.index_id  
WHERE it.internal_type_desc = 'XML_INDEX_NODES';  

H.H. 傳回 Service Broker 佇列的內部資料表中繼資料Return internal table metadata for Service Broker queues

SELECT AS queue_name  
    ,q.object_id AS queue_id  
    , AS internal_table_name  
    ,it.object_id AS internal_table_id  
FROM sys.internal_tables AS it  
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id  
WHERE it.internal_type_desc = 'QUEUE_MESSAGES';  

I.I. 傳回所有 Service Broker 服務的內部資料表中繼資料Return internal table metadata for all Service Broker services

FROM tempdb.sys.internal_tables   
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';  

另請參閱See Also

目錄檢視 (Transact-SQL) Catalog Views (Transact-SQL)
物件目錄檢視(Transact SQL)Object Catalog Views (Transact-SQL)