sys.internal_tables (Transact-SQL)
Collapse the table of content
Expand the table of content

sys.internal_tables (Transact-SQL)


Applies To: SQL Server 2014, SQL Server 2016 Preview

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.

Applies to: SQL Server (SQL Server 2008 through current version).

Column name

Data type


<Columns inherited from sys.objects>


For a list of columns that this view inherits, see sys.objects (Transact-SQL).



Type of the internal table:

201 = queue_messages

202 = xml_index_nodes

203 = fulltext_catalog_freelist

204 = fulltext_catalog_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



Description of the type of internal table:













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



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 



Non-zero value is the ID of data space (filegroup or partition-scheme) that holds the large object (LOB) data for this table.



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. For more information, see Metadata Visibility Configuration.

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.

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

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;

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;

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;

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;

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';

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';

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

Community Additions

© 2015 Microsoft