sys.internal_tables (Transact-SQL)sys.internal_tables (Transact-SQL)

SE APLICA A: síSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

Devuelve una fila por cada objeto que es una tabla interna.Returns one row for each object that is an internal table. Las tablas internas son generadas de forma automática por SQL ServerSQL Server a fin de permitir varias características.Internal tables are automatically generated by SQL ServerSQL Server to support various features. Por ejemplo, cuando se crea un índice XML principal, SQL ServerSQL Server crea automáticamente una tabla interna para hacer que persistan los datos del documento XML dividido.For example, when you create a primary XML index, SQL ServerSQL Server automatically creates an internal table to persist the shredded XML document data. Las tablas internas aparecen en la sys esquema de cada base de datos y tienen nombres únicos y generados por el sistema que indican su función, por ejemplo, xml_index_nodes_2021582240_32001 o queue_messages_1977058079Internal 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

Las tablas internas no contienen datos accesibles para el usuario y su esquema es fijo e inalterable.Internal tables do not contain user-accessible data, and their schema are fixed and unalterable. No se puede hacer referencia a los nombres de las tablas internas en las instrucciones de Transact-SQLTransact-SQL.You cannot reference internal table names in Transact-SQLTransact-SQL statements. Por ejemplo, no se puede ejecutar una instrucción como SELECT * FROM <sys.internal_table_name > .For example, you cannot execute a statement such as SELECT * FROM <sys.internal_table_name>. Sin embargo, se pueden consultar vistas de catálogo para ver los metadatos de las tablas internas.However, you can query catalog views to see the metadata of internal tables.

Nombre de columnaColumn name Tipo de datosData type DescripciónDescription
<Las columnas que se heredan de sys.objects ><Columns inherited from sys.objects> Para obtener una lista de columnas que hereda esta vista, consulte sys.objects (Transact-SQL).For a list of columns that this view inherits, see sys.objects (Transact-SQL).
internal_typeinternal_type tinyinttinyint Tipo de la tabla interna:Type of the internal table:

3 = query_disk_store_query_hints3 = query_disk_store_query_hints

4 = query_disk_store_query_template_parameterization4 = query_disk_store_query_template_parameterization

6 = query_disk_store_wait_stats6 = query_disk_store_wait_stats

201 = queue_messages201 = queue_messages

202 = xml_index_nodes202 = xml_index_nodes

203 = fulltext_catalog_freelist203 = fulltext_catalog_freelist

205 = query_notification205 = query_notification

206 = service_broker_map206 = service_broker_map

207 = extended_indexes (por ejemplo, un índice espacial)207 = extended_indexes (such as a spatial index)

208 = filestream_tombstone208 = filestream_tombstone

209 = change_tracking209 = change_tracking

210 = tracked_committed_transactions210 = tracked_committed_transactions

220 = contained_features220 = contained_features

225 = filetable_updates225 = filetable_updates

236 = selective_xml_index_node_table236 = selective_xml_index_node_table

240 = query_disk_store_query_text240 = query_disk_store_query_text

241 = query_disk_store_query241 = query_disk_store_query

242 = query_disk_store_plan242 = query_disk_store_plan

243 = query_disk_store_runtime_stats243 = query_disk_store_runtime_stats

244 = query_disk_store_runtime_stats_interval244 = query_disk_store_runtime_stats_interval

245 = query_context_settings245 = query_context_settings
internal_type_descinternal_type_desc nvarchar(60)nvarchar(60) Descripción del tipo de tabla interna:Description of the type of internal table:

QUERY_DISK_STORE_QUERY_HINTSQUERY_DISK_STORE_QUERY_HINTS

QUERY_DISK_STORE_QUERY_TEMPLATE_PARAMETERIZATIONQUERY_DISK_STORE_QUERY_TEMPLATE_PARAMETERIZATION

QUERY_DISK_STORE_WAIT_STATSQUERY_DISK_STORE_WAIT_STATS

QUEUE_MESSAGESQUEUE_MESSAGES

XML_INDEX_NODESXML_INDEX_NODES

FULLTEXT_CATALOG_FREELISTFULLTEXT_CATALOG_FREELIST

FULLTEXT_CATALOG_MAPFULLTEXT_CATALOG_MAP

QUERY_NOTIFICATIONQUERY_NOTIFICATION

SERVICE_BROKER_MAPSERVICE_BROKER_MAP

EXTENDED_INDEXESEXTENDED_INDEXES

FILESTREAM_TOMBSTONEFILESTREAM_TOMBSTONE

CHANGE_TRACKINGCHANGE_TRACKING

TRACKED_COMMITTED_TRANSACTIONSTRACKED_COMMITTED_TRANSACTIONS

CONTAINED_FEATURESCONTAINED_FEATURES

FILETABLE_UPDATESFILETABLE_UPDATES

SELECTIVE_XML_INDEX_NODE_TABLESELECTIVE_XML_INDEX_NODE_TABLE

QUERY_DISK_STORE_QUERY_TEXTQUERY_DISK_STORE_QUERY_TEXT

QUERY_DISK_STORE_QUERYQUERY_DISK_STORE_QUERY

QUERY_DISK_STORE_PLANQUERY_DISK_STORE_PLAN

QUERY_DISK_STORE_RUNTIME_STATSQUERY_DISK_STORE_RUNTIME_STATS

QUERY_DISK_STORE_RUNTIME_STATS_INTERVALQUERY_DISK_STORE_RUNTIME_STATS_INTERVAL

QUERY_CONTEXT_SETTINGSQUERY_CONTEXT_SETTINGS
parent_idparent_id intint Id. del primario, independientemente de si es de ámbito de esquema o no.ID of the parent, regardless of whether it is schema-scoped or not. Es 0 si no hay primario.Otherwise, 0 if there is no parent.

queue_messages = object_id de colaqueue_messages = object_id of queue

xml_index_nodes = object_id del índice xmlxml_index_nodes = object_id of the xml index

fulltext_catalog_freelist = fulltext_catalog_id del catálogo de texto completofulltext_catalog_freelist = fulltext_catalog_id of the full-text catalog

fulltext_index_map = object_id del índice de texto completofulltext_index_map = object_id of the full-text index

query_notification, o service_broker_map = 0query_notification, or service_broker_map = 0

extended_indexes = object_id de un índice extendido, como un índice espacialextended_indexes = object_id of an extended index, such as a spatial index

object_id de la tabla para la tabla que está habilitado el seguimiento = change_trackingobject_id of the table for which table tracking is enabled = change_tracking
parent_minor_idparent_minor_id intint Id. secundario del primario.Minor ID of the parent.

xml_index_nodes = index_id del índice XMLxml_index_nodes = index_id of the XML index

extended_indexes = index_id de un índice extendido, como un índice espacialextended_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, o change_tracking0 = queue_messages, fulltext_catalog_freelist, fulltext_index_map, query_notification, service_broker_map, or change_tracking
lob_data_space_idlob_data_space_id intint Un valor distinto de cero es el Id. del espacio de datos (grupo de archivos o esquema de partición) que almacena los datos de objetos grandes (LOB) para esta tabla.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_idfilestream_data_space_id intint Reservado para uso futuro.Reserved for future use.

PermisosPermissions

La visibilidad de los metadatos en las vistas de catálogo se limita a los elementos protegibles y que son propiedad de un usuario o sobre los que el usuario tiene algún permiso.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. Para obtener más información, consulte Metadata Visibility Configuration.For more information, see Metadata Visibility Configuration.

ComentariosRemarks

Las tablas internas se colocan en el mismo grupo de archivos que la entidad primaria.Internal tables are placed on the same filegroup as the parent entity. Puede usar la consulta del catálogo que se muestra en el ejemplo F a continuación para devolver el número de páginas que usan las tablas internas para los datos almacenados consecutivamente, no consecutivamente y de objetos grandes (LOB).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.

Puede usar el sp_spaceused procedimiento del sistema para devolver datos de uso de espacio para las tablas internas.You can use the sp_spaceused system procedure to return space usage data for internal tables. sp_spaceused informa del espacio de tabla interna de las maneras siguientes:sp_spaceused reports internal table space in the following ways:

  • Cuando se especifica un nombre de cola, se hace referencia a la tabla interna subyacente asociada a la cola y se informa del consumo del almacenamiento.When a queue name is specified, the underlying internal table associated with the queue is referenced and its storage consumption is reported.

  • Las páginas que se usan en las tablas internas de índices XML, índices espaciales y los índices de texto completo se incluyen en el index_size columna.Pages that are used by the internal tables of XML indexes, spatial indexes, and full-text indexes are included in the index_size column. Cuando se especifica una tabla o nombre de la vista indizada, las páginas de los índices XML, índices espaciales y los índices de texto completo para ese objeto se incluyen en las columnas reservada y index_size.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.

EjemplosExamples

En los ejemplos siguientes se demuestra cómo consultar los metadatos de las tablas internas de consulta mediante vistas de catálogo.The following examples demonstrate how to query internal table metadata by using catalog views.

A.A. Mostrar las tablas internas que heredan las columnas de la vista de catálogo sys.objectsShow internal tables that inherit columns from the sys.objects catalog view

SELECT * FROM sys.objects WHERE type = 'IT';  

b.B. Devolver todos los metadatos de las tablas internas (incluido los que se heredan de sys.objects)Return all internal table metadata (including that which is inherited from sys.objects)

SELECT * FROM sys.internal_tables;  

C.C. Devolver las columnas de las tablas internas y los tipos de datos de las columnasReturn 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.D. Devolver los índices de las tablas internasReturn 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.E. Devolver estadísticas de las tablas internasReturn 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.F. Devolver información de la unidad de asignación y de la partición de las tablas internasReturn 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.G. Devolver los metadatos de las tablas internas para los índices XMLReturn 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.H. Devolver los metadatos de las tablas internas para las colas de Service BrokerReturn 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.I. Devolver los metadatos de las tablas internas para todos los servicios de Service BrokerReturn internal table metadata for all Service Broker services

SELECT *   
FROM tempdb.sys.internal_tables   
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';  
GO  

Vea tambiénSee Also

Vistas de catálogo (Transact-SQL) Catalog Views (Transact-SQL)
Vistas de catálogo de objetos (Transact-SQL)Object Catalog Views (Transact-SQL)