sys.internal_tables (Transact-SQL)

Applies to: SQL Server

Returns one row for each object that is an internal table. Internal tables are automatically generated by SQL Server to support various features. For example, when you create a primary XML index, SQL Server 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 Transact-SQL 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

The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. 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)