sys-internal-tables-transact-sql.md

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
sys.internal_tables (Transact-SQL)	sys.internal_tables (Transact-SQL)	rwestMSFT	randolphwest	03/14/2019	sql	system-objects	reference	sys.internal_tables internal_tables sys.internal_tables_TSQL internal_tables_TSQL	internal tables sys.internal_tables catalog view	TSQL

sys.internal_tables (Transact-SQL)

[!INCLUDE SQL Server]

Returns one row for each object that is an internal table. Internal tables are automatically generated by [!INCLUDEssNoVersion] to support various features. For example, when you create a primary XML index, [!INCLUDEssNoVersion] automatically creates an internal table to persist the shredded XML document data. Internal 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. You cannot reference internal table names in [!INCLUDEtsql] statements. 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
<Columns inherited from sys.objects>		For a list of columns that this view inherits, see sys.objects (Transact-SQL).
internal_type	tinyint	Type of the internal table: 3 = query_disk_store_query_hints 4 = query_disk_store_query_template_parameterization 6 = query_disk_store_wait_stats 201 = queue_messages 202 = xml_index_nodes 203 = fulltext_catalog_freelist 205 = query_notification 206 = service_broker_map 207 = extended_indexes (such as a spatial index) 208 = filestream_tombstone 209 = change_tracking 210 = tracked_committed_transactions 220 = contained_features 225 = filetable_updates 236 = selective_xml_index_node_table 240 = query_disk_store_query_text 241 = query_disk_store_query 242 = query_disk_store_plan 243 = query_disk_store_runtime_stats 244 = query_disk_store_runtime_stats_interval 245 = query_context_settings
internal_type_desc	nvarchar(60)	Description of the type of internal table: QUERY_DISK_STORE_QUERY_HINTS QUERY_DISK_STORE_QUERY_TEMPLATE_PARAMETERIZATION QUERY_DISK_STORE_WAIT_STATS QUEUE_MESSAGES XML_INDEX_NODES FULLTEXT_CATALOG_FREELIST FULLTEXT_CATALOG_MAP QUERY_NOTIFICATION SERVICE_BROKER_MAP EXTENDED_INDEXES FILESTREAM_TOMBSTONE CHANGE_TRACKING TRACKED_COMMITTED_TRANSACTIONS CONTAINED_FEATURES FILETABLE_UPDATES SELECTIVE_XML_INDEX_NODE_TABLE QUERY_DISK_STORE_QUERY_TEXT QUERY_DISK_STORE_QUERY QUERY_DISK_STORE_PLAN QUERY_DISK_STORE_RUNTIME_STATS QUERY_DISK_STORE_RUNTIME_STATS_INTERVAL QUERY_CONTEXT_SETTINGS
parent_id	int	ID of the parent, regardless of whether it is schema-scoped or not. Otherwise, 0 if there is no parent. queue_messages = object_id of queue xml_index_nodes = object_id of the xml index fulltext_catalog_freelist = fulltext_catalog_id of the full-text catalog fulltext_index_map = object_id of the full-text index query_notification, or service_broker_map = 0 extended_indexes = object_id of an extended index, such as a spatial index object_id of the table for which table tracking is enabled = change_tracking
parent_minor_id	int	Minor ID of the parent. xml_index_nodes = index_id of the XML index extended_indexes = index_id of an extended index, such as a spatial index 0 = queue_messages, fulltext_catalog_freelist, fulltext_index_map, query_notification, service_broker_map, or change_tracking
lob_data_space_id	int	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_id	int	Reserved for future use.

Permissions

[!INCLUDEssCatViewPerm] For more information, see Metadata Visibility Configuration.

Remarks

Internal tables are placed on the same filegroup as the parent entity. 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.

You can use the sp_spaceused system procedure to return space usage data for internal tables. 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.

• Pages that are used by the internal tables of XML indexes, spatial indexes, and full-text indexes are included in the index_size column. When 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.

Examples

The following examples demonstrate how to query internal table metadata by using catalog views.

A. Show internal tables that inherit columns from the sys.objects catalog view

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

B. Return all internal table metadata (including that which is inherited from sys.objects)

SELECT * FROM sys.internal_tables;  

C. Return internal table columns and column data types

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    ,itab.name AS internal_table_name  
    ,typ.name AS column_data_type   
    ,col.*  
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 itab.name, col.column_id;  

D. Return internal table indexes

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    , itab.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 itab.name, idx.index_id;  

E. Return internal table statistics

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    ,itab.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 itab.name, s.stats_id;  

F. Return internal table partition and allocation unit information

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name  
    ,itab.name AS internal_table_name  
    ,idx.name AS heap_or_index_name  
    ,p.*  
    ,au.*  
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   
    END  
ORDER BY itab.name, idx.index_id;  

G. Return internal table metadata for XML indexes

SELECT t.name AS parent_table  
    ,t.object_id AS parent_table_id  
    ,it.name AS internal_table_name  
    ,it.object_id AS internal_table_id  
    ,xi.name 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';  
GO  

H. Return internal table metadata for Service Broker queues

SELECT q.name AS queue_name  
    ,q.object_id AS queue_id  
    ,it.name 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';  
GO  

I. Return internal table metadata for all Service Broker services

SELECT *   
FROM tempdb.sys.internal_tables   
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';  
GO  

See Also

Catalog Views (Transact-SQL)
Object Catalog Views (Transact-SQL)