Tabelas internas
SQL Server cria automaticamente tabelas internas para oferecer suporte aos seguintes recursos:
Índices XML primários
Índices espaciais
Service Broker
Notificações de consultas
Controle de alterações
As tabelas internas são um efeito colateral de alguma outra ação do usuário. Por exemplo, quando você cria um índice XML primário, o SQL Server cria automaticamente uma tabela interna para manter os dados fragmentados do documento XML. As tabelas internas aparecem no esquema sys de cada um dos bancos de dados e têm nomes únicos gerados pelo sistema que indicam sua função, por exemplo, xml_index_nodes_2021582240_32001 ou queue_messages_1977058079.
As tabelas internas não contêm dados acessíveis ao usuário, e seus esquemas são fixos e inalteráveis. Você não pode fazer referências a nomes de tabelas internas em instruções Transact-SQL. Por exemplo, você não pode executar uma instrução como SELECT * FROM <sys.internal_table_name>. Entretanto, você pode consultar as exibições do catálogo para ver os metadados das tabelas internas.
Visualizando os metadados de uma tabela interna
Você pode ver os metadados associados às tabelas internas utilizando a exibição do catálogo sys.internal_tables. Usando esta visualização, você pode ver o esquema das tabelas internas. Em razão das tabelas internas possuírem muitas das características das tabelas de usuários, a exibição de sys.internal_tables herda colunas da exibição do catálogo sys.objects e tem um tipo de "IT". Assim como as tabelas de usuário, a coluna de metadados para as tabelas internas está visível na exibição do catálogo sys.columns, e os metadados para índices e estatísticas gerados pelo sistema estão visíveis no sys.indexes e na exibições do catálogos do sys.stats.
Ao unir as outras exibições do catálogo, também se pode obter as informações sobre alocação e utilização de espaços. Consulte "Armazenamento de Tabela Interna" nesse tópico.
A ilustração seguinte mostra o modelo de dados de catálogo de alto nível.
.gif "Diagrama de exibições de catálogo de tabela interna")
Permissões para Visualizar os Metadados de Tabela Interna
Para visualizar os metadados de uma tabela interna em um banco de dados, você precisa de uma das seguintes permissões ou associação de grupo:
Permissão CONTROL SERVER.
Permissão CONTROL no banco de dados.
Associação no db_owner ou grupo sysadmin.
Um usuário que pode exibir a entidade pai (o índice XML ou espacial ou a fila) pode exibir a tabela interna para aquela entidade.
Visualizando metadados de índice XML
A seguinte ilustração mostra a estrutura de metadados para uma tabela interna ou um índice XML.
.gif "Diagrama de exibições de catálogo do índice XML")
Para entender as relações de exibição de catálogo mostradas na ilustração, assuma que um índice XML primário Xp é criado na tabela T. Os metadados da tabela estão na exibição do catálogo sys.tables e os metadados do índice XML estão na exibição do catálogo sys.xml_indexes. Os metadados da tabela interna Ti criados pelo SQL Server para manter os dados de índice XML estão na exibição sys.internal_tables.
Para encontrar a relação entre a tabela interna Ti e a tabela de usuário T, você pode unir a coluna parent_id da exibição sys.internal_tables para a coluna object_id da exibição sys.tables. Para encontrar a relação entre a tabela interna Ti e o índice XML Xp, você pode unir as colunas parent_id e parent_minor_id de sys.internal_tables às colunas object_id e index_id de sys.xml_indexes. Veja o exemplo G abaixo.
Visualizando metadados de índice espacial
Os metadados de índices espaciais são, em sua grande parte, equivalentes aos metadados de índices XML. As diferenças são que os índices espaciais usam sys.spatial_indexes em vez de sys.xml_indexes, e você precisa utilizar sys.spatial_index_tessellations para visualizar os parâmetros espaciais para um índice espacial.
A seguinte ilustração mostra a estrutura de metadados para uma tabela interna ou um índice espacial.
.gif "Diagrama de exibições de catálogo do índice espacial")
Para entender as relações de exibição de catálogo mostradas na ilustração, assuma que um índice espacial, Si, é criado na tabela T. Os metadados da tabela estão na exibição do catálogo sys.tables e os metadados do índice espacial estão nas exibições de catálogo sys.spatial_indexes e sys.spatial_index_tessellations. Os metadados para a tabela interna Ti criados pelo SQL Server para manter os dados de índice espacial estão na exibição sys.internal_tables.
Para encontrar a relação entre a tabela interna Ti e a tabela de usuário T, você pode unir a coluna parent_id da exibição sys.internal_tables para a coluna object_id da exibição sys.tables. Para encontrar a relação entre a tabela interna Ti e o índice espacial Si, você pode unir as colunas parent_id e parent_minor_id de sys.internal_tables às colunas object_id e index_id de sys.spatial_indexes. Para obter mais informações, veja exemplo L, neste tópico.
Visualizando metadados do Service Broker
A ilustração seguinte mostra a estrutura de metadados para uma tabela interna em uma fila do Service Broker. As mensagens, notificações de consulta e notificações de evento do Service Broker utilizam as filas do Service Broker. Além disso, o recurso Service Broker também utiliza uma tabela interna para armazenar informações sobre todos os serviços do Service Broker em todos os bancos de dados. Essa tabela interna está no banco de dados do sistema tempdb.
.gif "Diagrama de exibições de catálogo do service broker")
Visualizando metadados de notificação de consulta
A ilustração seguinte mostra a estrutura de metadados para uma tabela interna em uma assinatura de notificação de consulta. São usadas tabelas internas para armazenar os parâmetros da assinatura de notificação de consulta.
.gif "Diagrama de exibições de catálogo de notificação de consulta")
Armazenamento de tabela interna
Tabelas internas são colocadas no mesmo grupo de arquivos da entidade de pai. Você pode usar a consulta de catálogo mostrada no Exemplo F abaixo para retornar o número de páginas que as tabelas internas consomem para dados de dentro da linha, fora da linha, e de objetos grandes (LOB).
Você pode usar o procedimento de sistema sp_spaceused para retornar os dados de uso do espaço para as tabelas internas. sp_spaceused reporta o espaço da tabela interna das seguintes maneiras:
Quando um nome de fila é especificado, a tabela interna subjacente associada à fila é referenciada, e seu consumo de armazenamento é reportado.
Páginas usadas pelas tabelas internas de índices XML, espaciais e de texto completo são incluídas na coluna index_size. Quando um nome de exibição indexada ou tabela for especificado, as páginas dos índices XML, espaciais e de texto completo daquele objeto serão incluídas nas colunas reserved e index_size.
Exemplos
Os exemplos seguintes demonstram como consultar metadados de tabela interna usando as exibições do catálogo.
A. Mostrar tabelas internas que herdam colunas da exibição do catálogo sys.objects
SELECT * FROM sys.objects WHERE type = 'IT';
B. Retorna todos os metadados de tabela interna (inclusive os herdados de sys.objects)
SELECT * FROM sys.internal_tables;
C. Retorna as colunas de tabela interna e os tipos de dados da coluna
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. Retorna os índices de tabela interna
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. Retorna as estatísticas da tabela interna
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. Retorna a partição da tabela interna e informações da unidade de alocação
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. Retorna os metadados da tabela interna para os índices 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. Retorna os metadados da tabela interna para as filas do 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. Retorna os metadados de tabela interna para todos os serviços do Service Broker
SELECT *
FROM tempdb.sys.internal_tables
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';
GO
J. Retorna os metadados da tabela interna para assinaturas de notificação de consulta
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. Retorna os metadados da tabela interna para os índices espaciais
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