• [アーティクル]

内部テーブル

SQL Server では、次の機能をサポートする内部テーブルが自動的に作成されます。

  • プライマリ XML インデックス

  • 空間インデックス

  • Service Broker

  • クエリ通知

  • 変更の追跡

内部テーブルは、一部のユーザー操作の副作用として生じます。 たとえば、プライマリ XML インデックスを作成すると、SQL Server によって内部テーブルが自動的に作成され、細分化された XML ドキュメント データが保存されます。 内部テーブルは、各データベースの sys スキーマに表示され、そのテーブルの機能を示す一意名 (xml_index_nodes_2021582240_32001 や queue_messages_1977058079 など) がシステムによって生成されます。

内部テーブルには、ユーザーがアクセスできるデータは含まれていません。また、そのスキーマは固定され変更できません。 内部テーブルの名前を Transact-SQL ステートメントで参照することはできません。 たとえば、SELECT * FROM <sys.internal_table_name> のようなステートメントは実行できません。 ただし、カタログ ビューにクエリを実行して、内部テーブルのメタデータを表示することはできます。

内部テーブルのメタデータの表示

内部テーブルに関連付けられたメタデータは、sys.internal_tables カタログ ビューを使用して表示できます。 このビューを使用すると、内部テーブルのスキーマを表示できます。 内部テーブルにはユーザー テーブルと同じ特性が多数あるので、sys.internal_tables ビューは、sys.objects カタログ ビューから列を継承し、型は 'IT' になります。 内部テーブルの列のメタデータはユーザー テーブルと同様に、sys.columns カタログ ビューに表示されます。また、内部テーブル上のシステム生成のインデックスや統計のメタデータは、sys.indexes カタログ ビューと sys.stats カタログ ビューに表示されます。

他のカタログ ビューに結合して、割り当てや領域の使用量に関する情報を入手することもできます。 これについては「内部テーブルのストレージ」を参照してください。

次の図は、最上位レベルのカタログ データ モデルを示します。

内部テーブルのカタログ ビューの図

内部テーブルのメタデータを表示する権限

データベース内の内部テーブルのメタデータを表示するには、次の権限があるか、次のグループに所属している必要があります。

  • CONTROL SERVER 権限。

  • データベースの CONTROL 権限。

  • db_owner グループまたは sysadmin グループのメンバーシップ。

  • 親エンティティ (XML インデックス、空間インデックス、またはキュー) を表示できるユーザー。このユーザーは、そのエンティティの内部テーブルを表示できます。

XML インデックスのメタデータの表示

次の図は、XML インデックスに関する内部テーブルのメタデータ構造を示しています。

XML インデックス カタログ ビューの図

この図に示されているカタログ ビューの関係を理解するために、プライマリ XML インデックス Xp がテーブル T に作成されているとします。 このテーブルのメタデータは sys.tables カタログ ビューに含まれ、XML インデックスのメタデータは sys.xml_indexes カタログ ビューに含まれます。 XML インデックスのデータを保存するために SQL Server で作成される内部テーブル Ti のメタデータは、sys.internal_tables ビューに含まれます。

内部テーブル Ti とユーザー テーブル T とのリレーションシップを確認するには、sys.internal_tables ビューの parent_id 列を sys.tables ビューの object_id 列に結合します。 内部テーブル Ti と XML インデックス Xp とのリレーションシップを確認するには、sys.internal_tables の parent_id 列と parent_minor_id 列を、sys.xml_indexes の object_id 列と index_id 列に結合します。 後半の例 G を参照してください。

空間インデックスのメタデータの表示

空間インデックスのメタデータは、XML インデックスのメタデータとほぼ同じです。 両者の違いは、空間インデックスでは、sys.xml_indexes ではなく sys.spatial_indexes が使用される点、および、空間インデックスの空間パラメーターを確認する際に sys.spatial_index_tessellations を使用する必要がある点です。

次の図は、空間インデックスに関する内部テーブルのメタデータ構造を示しています。

空間インデックス カタログ ビューの図

この図に示されているカタログ ビューの関係を理解するために、空間インデックス Si がテーブル T に作成されているとします。 テーブルのメタデータは sys.tables カタログ ビューに、空間インデックスのメタデータは sys.spatial_indexes カタログ ビューと sys.spatial_index_tessellations カタログ ビューに存在します。 空間インデックスのデータを保存するために SQL Server で作成される内部テーブル Ti のメタデータは、sys.internal_tables ビューに含まれます。

内部テーブル Ti とユーザー テーブル T とのリレーションシップを確認するには、sys.internal_tables ビューの parent_id 列を sys.tables ビューの object_id 列に結合します。 内部テーブル Ti と空間インデックス Si とのリレーションシップを確認するには、sys.internal_tables の parent_id 列と parent_minor_id 列を、sys.spatial_indexes の object_id 列と index_id 列に結合します。 詳細については、後の「例 L」を参照してください。

Service Broker のメタデータの表示

次の図は、Service Broker キューに関する内部テーブルのメタデータ構造を示しています。 Service Broker のメッセージ、クエリ通知、およびイベント通知は、Service Broker キューを使用します。 また、Service Broker 機能では、全データベース内のすべての Service Broker サービスに関する情報を格納するために内部テーブルも使用します。 この内部テーブルは、tempdb システム データベースに含まれます。

Service Broker のカタログ ビューの図

クエリ通知のメタデータの表示

次の図は、クエリ通知サブスクリプションに関する内部テーブルのメタデータ構造を示しています。 内部テーブルは、クエリ通知サブスクリプションのパラメーターを保存するために使用されます。

クエリ通知カタログ ビューの図

内部テーブルのストレージ

内部テーブルは、親エンティティと同じファイル グループに配置されます。 後半の例 F で示すカタログ クエリを使用して、内部テーブルが行内データ、行外データ、およびラージ オブジェクト (LOB) データに使用するページ数を返すことができます。

sp_spaceused システム プロシージャを使用すると、内部テーブルの使用領域に関するデータを返すことができます。sp_spaceused は、次の方法で内部テーブルの領域についてレポートします。

  • キュー名を指定すると、キューに関連付けられた基になる内部テーブルが参照され、そのストレージ使用量がレポートされます。

  • XML インデックス、空間インデックス、およびフルテキスト インデックスの内部テーブルに使用されているページ数は、index_size 列に含まれます。 テーブルまたはインデックス付きビューの名前を指定すると、そのオブジェクトの XML インデックス、空間インデックス、およびフルテキスト インデックスのページ数は reserved 列と index_size 列にそれぞれ含まれます。

例

次の例では、カタログ ビューを使用して内部テーブルのメタデータにクエリを実行する方法について説明します。

A. sys.objects カタログ ビューから列を継承する内部テーブルを表示する

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

B. すべての内部テーブルのメタデータ (sys.objects から継承される内容を含む) を返す

SELECT * FROM sys.internal_tables;

C. 内部テーブルの列と列のデータ型を返す

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. 内部テーブルのインデックスを返す

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. 内部テーブルの統計情報を返す

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. 内部テーブルのパーティションとアロケーション ユニットの情報を返す

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. 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. 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. すべての Service Broker サービスに関する内部テーブルのメタデータを返す

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

J. クエリ通知サブスクリプションに関する内部テーブルのメタデータを返す

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. 空間インデックスに関する内部テーブルのメタデータを返す

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