sp_helpindexを全DB全テーブルに実行してインデックス情報を一覧にするクエリ

前提

検証用に本番機のデータベースをテスト環境に作りたくて、

「スクリプトの生成」を使ってデータベース全体の作成クエリを作ったあと、データを本番機から流し込む、

という方法でやっていたら、インデックス作成クエリが出力されていなくてテストで失敗した。

本番機と比較して追加漏れを確認するために、このクエリを書いた。

クエリ

ざっくりと動作を説明すると sp_MSforeachdb と sp_MSforeachtable をネストし、sp_helpindex でインデックス情報を一時テーブルに格納し、選択する。

-- 一時テーブル 
CREATE TABLE #temp_index_info (a text, b text, c text)
CREATE TABLE #index_info (server_name text, db_name text, table_name text, index_name text, index_description text, index_keys text)

-- データベースは ! を使い、テーブルは ? を使うことで全DB全テーブルに対して処理をする。
EXEC sp_MSforeachdb @replacechar ='!', @command1='
    USE [!];
    EXEC sp_MSforeachtable ''
        INSERT #temp_index_info EXEC sp_helpindex ''''?'''';
        INSERT #index_info SELECT @@SERVERNAME, ''''!'''', ''''?'''', * FROM #temp_index_info;
        TRUNCATE TABLE #temp_index_info;
    ''
';
DROP TABLE #temp_index_info;

SELECT * FROM #index_info;
DROP TABLE #index_info;

学び

今回は sp_MSforeachdb , sp_MSforeachtableを初めて活用した。

めっちゃ便利。これからも使っていく。

あと、弊社内でもこういうインデックスの情報とかをまとめて簡単に参照できるウェブページが作れるといいね。

設定情報に加えて意図や目的をメモする欄とか入れたりして、DB自体の情報に対して説明を入れ込むことがしたい。

属人化も減るし、社内メンバーの理解度の底上げにもなると思う。

以上！