--- title: "sys.internal_tables (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "06/10/2016" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords: - "sys.internal_tables" - "internal_tables" - "sys.internal_tables_TSQL" - "internal_tables_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "internal tables" - "sys.internal_tables catalog view" ms.assetid: a5821c70-f150-4676-8476-3a31f7403dca caps.latest.revision: 52 author: "BYHAM" ms.author: "rickbyh" manager: "jhubbard" --- # sys.internal_tables (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-xxxx-xxxx-xxx_md](../../includes/tsql-appliesto-ss2008-xxxx-xxxx-xxx-md.md)] Returns one row for each object that is an internal table. Internal tables are automatically generated by [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] to support various features. For example, when you create a primary XML index, [!INCLUDE[ssNoVersion](../../includes/ssnoversion-md.md)] 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 [!INCLUDE[tsql](../../includes/tsql-md.md)] statements. For example, you cannot execute a statement such as SELECT \* FROM *\*. However, you can query catalog views to see the metadata of internal tables. |Column name|Data type|Description| |-----------------|---------------|-----------------| |**\**||For a list of columns that this view inherits, see [sys.objects (Transact-SQL)](../../relational-databases/system-catalog-views/sys-objects-transact-sql.md).| |**internal_type**|**tinyint**|Type of the internal table:

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**| |**internal_type_desc**|**nvarchar(60)**|Description of the type of internal table:

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| |**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 [!INCLUDE[ssCatViewPerm](../../includes/sscatviewperm-md.md)] For more information, see [Metadata Visibility Configuration](../../relational-databases/security/metadata-visibility-configuration.md). ## 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](../../relational-databases/system-stored-procedures/sp-spaceused-transact-sql.md) 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)](../../relational-databases/system-catalog-views/catalog-views-transact-sql.md) [Object Catalog Views (Transact-SQL)](../../relational-databases/system-catalog-views/object-catalog-views-transact-sql.md)