Tables internes

Affichage des métadonnées des tables internes

Vous pouvez consulter les métadonnées associées aux tables internes à l'aide de l'affichage catalogue sys.internal_tables. Cet affichage vous permet de voir le schéma des tables internes. Étant donné que les tables internes possèdent de nombreuses caractéristiques identiques à celles des tables utilisateur, l'affichage sys.internal_tables hérite des colonnes de l'affichage catalogue sys.objects et est de type « IT » (table interne). Comme pour les tables utilisateur, les métadonnées relatives aux colonnes des tables internes sont visibles dans l'affichage catalogue sys.columns et les métadonnées relatives aux index et statistiques générés par le système pour les tables internes sont visibles dans les affichages catalogue sys.indexes et sys.stats.

En joignant ces affichages à d'autres affichages catalogue, vous pouvez également obtenir des informations sur les unités d'allocation et l'utilisation de l'espace. Voir « Stockage des tables internes » plus loin dans cette rubrique.

L'illustration suivante représente le modèle de données de catalogue de niveau supérieur.

Autorisations d'affichage des métadonnées des tables internes

Affichage des métadonnées des index XML

L'illustration suivante représente la structure des métadonnées pour une table interne d'un index XML.

Afin de comprendre les relations des affichages catalogue indiquées dans l'illustration, supposons qu'un index XML primaire Xp est créé sur la table T. Les métadonnées associées à la table se trouvent dans l'affichage catalogue sys.tables et les métadonnées associées à l'index XML se trouvent dans l'affichage catalogue sys.xml_indexes. Les métadonnées de la table interne Ti, créée par SQL Server afin d'assurer la persistance des données de l'index XML, se trouvent dans l'affichage sys.internal_tables.

Pour identifier la relation entre la table interne Ti et la table utilisateur T, vous pouvez joindre la colonne parent_id de l'affichage sys.internal_tables à la colonne object_id de l'affichage sys.tables. Pour identifier la relation entre la table interne Ti et l'index XML Xp, vous pouvez joindre les colonnes parent_id et parent_minor_id de sys.internal_tables aux colonnes object_id et index_id de sys.xml_indexes. Voir exemple G ci-dessous.

Affichage des métadonnées des index spatiaux

Les métadonnées des index spatiaux sont quasiment identiques aux métadonnées des index XML. La différence est que les index spatiaux utilisent sys.spatial_indexes plutôt que sys.xml_indexes et que vous devez utiliser sys.spatial_index_tessellations pour consulter les paramètres spatiaux d'un index spatial.

L'illustration suivante représente la structure des métadonnées pour une table interne d'un index spatial.

Afin de comprendre les relations des affichages catalogue indiquées sur l'illustration, supposons qu'un index spatial Si est créé sur la table T. Les métadonnées de la table sont dans l'affichage catalogue sys.tables et les métadonnées de l'index spatial sont dans les affichages catalogue sys.spatial_indexes et sys.spatial_index_tessellations. Les métadonnées de la table interne Ti, créée par SQL Server afin de conserver les données de l'index spatial, se trouvent dans l'affichage sys.internal_tables.

Pour identifier la relation entre la table interne Ti et la table utilisateur T, vous pouvez joindre la colonne parent_id de l'affichage sys.internal_tables à la colonne object_id de l'affichage sys.tables. Pour identifier la relation entre la table interne Ti et l'index spatial Si, vous pouvez joindre les colonnes parent_id et parent_minor_id de sys.internal_tables aux colonnes object_id et index_id de sys.spatial_indexes. Pour plus d'informations, consultez l'exemple L, plus loin dans cette rubrique.

Affichage des métadonnées de Service Broker

L'illustration suivante représente la structure des métadonnées pour une table interne d'une file d'attente Service Broker. Les messages, notifications de requêtes et notifications d'événements de Service Broker utilisent des files d'attente Service Broker. En outre, la fonctionnalité Service Broker utilise également une table interne pour stocker des informations relatives à l'ensemble des services Service Broker dans toutes les bases de données. Cette table interne se trouve dans la base de données système tempdb.

Affichage des métadonnées des notifications de requêtes

L'illustration suivante représente la structure des métadonnées pour une table interne d'un abonnement aux notifications de requêtes. Les tables internes servent à stocker les paramètres de l'abonnement aux notifications de requêtes.

Stockage des tables internes

Les tables internes sont placées dans le même groupe de fichiers que l'entité parente. Vous pouvez utiliser la requête de catalogue présentée dans l'exemple F ci-dessous pour retourner le nombre de pages que les tables internes utilisent pour les données sur ligne, hors ligne et LOB.

Vous pouvez utiliser la procédure système sp_spaceused pour retourner des données relatives à l'utilisation de l'espace dans les tables internes. sp_spaceused fournit des informations sur l'espace utilisé dans les tables internes selon les principes suivants :

• Si un nom de file d'attente est spécifié, la table interne sous-jacente associée à la file d'attente est référencée et sa consommation de stockage est renseignée.

• Les pages utilisées par les tables internes des index XML, des index spatiaux et des index de texte intégral sont incluses dans la colonne index_size. Si un nom de table ou de vue indexée est spécifié, les pages des index XML, des index spatiaux et des index de texte intégral de cet objet sont incluses dans les colonnes reserved et index_size.

Exemples

Les exemples suivants montrent comment interroger les métadonnées des tables internes à l'aide des affichages catalogue.

A. Afficher les tables internes qui héritent des colonnes de l'affichage catalogue sys.objects

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

B. Retourner toutes les métadonnées des tables internes (y compris celles qui sont héritées de sys.objects)

SELECT * FROM sys.internal_tables;

C. Retourner les colonnes et les types de données des colonnes des tables internes

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. Retourner les index des tables internes

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. Retourner les statistiques des tables internes

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. Retourner les informations relatives aux partitions et aux unités d'allocation des tables internes

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. Retourner les métadonnées des tables internes pour les index XML

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. Retourner les métadonnées des tables internes pour les files d'attente Service Broker

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. Retourner les métadonnées des tables internes pour tous les services Service Broker

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

J. Retourner les métadonnées des tables internes pour les abonnements aux notifications de requêtes

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

K. Retourner les métadonnées des tables internes pour les index spatiaux

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
    ,si.name AS spatial_index_name
    ,si.index_id as spatial_index_id
FROM sys.internal_tables AS it
JOIN sys.tables AS t 
    ON it.parent_id = t.object_id
JOIN sys.spatial_indexes AS si 
    ON it.parent_id = si.object_id
    AND it.parent_minor_id  = si.index_id
WHERE it.internal_type_desc = 'EXTENDED_INDEXES';
GO

Voir aussi

Concepts