読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

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

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

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

 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

f:id:koogucc11:20140820015707p:plain

図1:クエリの結果

まとめ

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