sys-internal-tables-transact-sql.md

title	sys.internal_tables (Transact-SQL)
description	sys.internal_tables (Transact-SQL)
author	rwestMSFT
ms.author	randolphwest
ms.date	07/07/2025
ms.service	sql
ms.subservice	system-objects
ms.topic	reference
f1_keywords	sys.internal_tables internal_tables sys.internal_tables_TSQL internal_tables_TSQL
helpviewer_keywords	internal tables sys.internal_tables catalog view
dev_langs	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 [!INCLUDE ssNoVersion] to support various features. For example, when you create a primary XML index, [!INCLUDE ssNoVersion] 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.

Column name	Data type	Description
Columns inherited from sys.objects		For a list of columns that this view inherits, see sys.objects.
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 204 = FULLTEXT_INDEX_MAP 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_INDEX_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's schema-scoped or not. Otherwise, 0 if there's 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.

Internal tables don't contain user-accessible data, and their schema are fixed and unalterable. You can't reference internal table names in [!INCLUDE tsql] statements. For example, you can't execute a statement such as SELECT * FROM <sys.internal_table_name>. However, you can query catalog views to see the metadata of internal tables.

Permissions

[!INCLUDE ssCatViewPerm] 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 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
     INNER JOIN sys.columns AS col
         ON itab.object_id = col.object_id
     INNER 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
     INNER 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
     INNER 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
     INNER 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)
     INNER JOIN sys.partitions AS p
         ON p.object_id = idx.object_id
        AND p.index_id = idx.index_id
     INNER 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
     INNER JOIN sys.tables AS t
         ON it.parent_id = t.object_id
     INNER 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
     INNER 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

Related content

• System catalog views (Transact-SQL)
• Object catalog views (Transact-SQL)