インデックスと付加列を列挙してみる

今回のやることを説明してみる

　あるデータベースに存在する、インデックスの一覧を抽出するクエリを作成し、動作確認をします。この手のクエリは、できなくて困っている人が結構いますね。実現するために抽出プログラムをゴリゴリ書いたりすることも多いかと思います。

使用する環境を説明してみる

　Microsot Azure 上で 動作している Windows Server 2012 R2、SQL Server 2014 で動作確認をします。

実際にためしてみる

　下記のクエリを SQL Server Management Studio で実行します。クエリの結果は、図1のようになります。

　SELECT a + ',' FROM tbl + ',' FOR XML PATH('') で、取得した行のデータを連結することができます。それらの結果を、それぞれ CROSS APPLY し、結果を取得します。取得したデータには、末尾に余計な『,』が付加されているので、SUBSTRING で除去します。( うーん、少し格好悪い..... ) また、sys.objects の modify_date を利用して、現在日付から遡って情報を取得できるようになっています。

SELECT [データベース名] = DB_NAME(),
       [インデックス名] = i.name,
       [インデックスタイプ] = i.type_desc,
       [スキーマ名] = s.name,
       [テーブル名] = t.name,
       [インデックスな列達] = SUBSTRING(indnm,1,LEN(indnm) - 1),
       [付加な列達] = SUBSTRING(incnm,1,LEN(incnm) - 1)
FROM sys.indexes i
INNER JOIN sys.tables t
    ON i.object_id = t.object_id
INNER JOIN sys.schemas s
    ON t.schema_id = s.schema_id
INNER JOIN sys.objects o
    ON i.object_id = o.object_id
CROSS APPLY 
(
    SELECT c.name + ','
    FROM sys.index_columns ic
    INNER JOIN sys.columns c 
        ON ic.object_id = c.object_id 
            AND ic.column_id = c.column_id
    WHERE i.object_id = ic.object_id 
        AND i.index_id = ic.index_id
            AND ic.is_included_column = 0
    FOR XML PATH('')
) AS cra1(indnm)
CROSS APPLY 
(
    SELECT c.name + ','
    FROM sys.index_columns ic
    INNER JOIN sys.columns c 
        ON ic.[object_id] = c.object_id 
            AND ic.column_id = c.column_id
    WHERE i.object_id = ic.object_id 
        AND i.index_id = ic.index_id
            AND ic.is_included_column = 1
    FOR XML PATH('')
) AS cra2 (incnm)
WHERE o.modify_date > GETDATE() - 10

図1：クエリの結果

まとめ

　この手のクエリ、結構便利ですよね。今後もクエリ充実させたいと思います。