次の方法で共有

  • [アーティクル]

内部テーブル

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