Share via

Internal Tables

  • Article

New: 14 April 2006

SQL Server automatically creates internal tables to support the following features:

  • Full-text search
  • Query notifications
  • Primary XML indexes
  • Service Broker

Internal tables are a side effect of some other user action. 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.

Viewing Internal Table Metadata

You can view the metadata associated with internal tables by using the sys.internal_tables catalog view. Using this view, you can view the schema of the internal tables. Because internal tables have many of the same characteristics as user tables, the sys.internal_tables view inherits columns from the sys.objects catalog view and have a type of 'IT'. As with user tables, column metadata for internal tables is visible in the sys.columns catalog view, and the metadata for system-generated indexes and statistics on the internal tables is visible in the sys.indexes and sys.stats catalog views.

By joining to other catalog views, information about allocation and space utilization can also be obtained. See "Internal Table Storage" later in this topic.

The following illustration shows the top-level catalog data model.

Diagram of internal table catalog views

Permissions to View Internal Table Metadata

To view internal table metadata in a database, you need one of the following permissions or group memberships:

  • CONTROL SERVER permission.
  • CONTROL permission in the database.
  • Membership in the db_owner or sysadmin group.
  • A user that can view the parent entity (the XML index, queue, or full-text catalog) can view the internal table for that entity.

Viewing XML Index Metadata

The following illustration shows the metadata structure for an internal table on an XML index.

Diagram of XML index catalog views

To understand the catalog view relationships shown in the illustration, assume that a primary XML index Xp is created on table T. The metadata for the table is in the sys.tables catalog view and the metadata for the XML index is in the sys.xml_indexes catalog view. The metadata for the internal table Ti created by SQL Server to persist the XML index data is in the sys.internal_tables view.

To find the relationship between the internal table Ti and the user table T, you can join the parent_id column of the sys.internal_tables view to the object_id column of the sys.tables view. To find the relationship between the internal table Ti and the XML index Xp, you can join the parent_id and parent_minor_id columns of sys.internal_tables to the object_id and index_id columns of sys.xml_indexes. See example G below.

Viewing Service Broker Metadata

The following illustration shows the metadata structure for an internal table on an Service Broker queue. Service Broker messages, query notifications and event notifications use Service Broker queues. In addition, the Service Broker feature also uses an internal table to store information about all of the Service Broker services in all of the databases. This internal table is in the tempdb system database.

Diagram of service broker catalog views

Viewing Full-Text Catalog Metadata

The following illustration shows the metadata structure for an internal table on full-text catalogs and indexes. Internal tables are used to store the free list of a full-text catalog and the document map of a full-text index. The free list stores unused document IDs. A document ID is a 4-byte integer used internally to uniquely identify a document. The document map internal table stores a mapping between a full-text key and a document ID.

Diagram of full-text catalog views

Viewing Query Notification Metadata

The following illustration shows the metadata structure for an internal table on a query notification subscription. Internal tables are used to store the parameters of the query notification subscription.

Diagram of query notification catalog views

Internal Table Storage

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

J. Return internal table metadata for full-text catalogs and indexes

--
-- Internal table for full-text catalog free list
--
SELECT ftc.name AS fulltext_catalog_name
    ,ftc.fulltext_catalog_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.fulltext_catalogs AS ftc ON it.parent_id = ftc.fulltext_catalog_id
WHERE it.internal_type_desc = 'FULLTEXT_CATALOG_FREELIST' ;
GO
--
-- Internal table for full-text document map
--
SELECT OBJECT_NAME(fti.object_id) AS table_containing_fulltext_index
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.fulltext_indexes AS fti ON it.parent_id = fti.object_id
WHERE it.internal_type_desc = 'FULLTEXT_INDEX_MAP';
GO

K. Return internal table metadata for query notification subscriptions

SELECT qn.id AS query_subscription_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id
WHERE it.internal_type_desc = 'QUERY_NOTIFICATION';

See Also

Concepts

Indexes on xml Data Type Columns
Understanding Event Notifications
Using Query Notifications

Other Resources

Queues
Full-Text Search Architecture

Help and Information

Getting SQL Server 2005 Assistance