SQLServer: 一列目のカラムが重複しているインデックスを探すSQL

ディスク容量を減らしたいときや、更新性能UPのために不要なインデックスの候補を見つける方法です。

未使用インデックスの調査とは別に、既存のインデックスでひとまとめにできたり削除できるインデックスがないかを調べたいと思い、以下のクエリを作成しました。

一列目のカラムが重複しているインデックスは、場合によってはまとめられたり片方削除しても大丈夫だよね、という発想です。
最終的には開発者の判断が必要ですが、実際にこのクエリをベースにして多少のディスク容量削減を実現できました。

SELECT
   mainO.name as tableName --テーブル名
  ,mainO.object_id as tableID
  ,mainI.name as indexName --インデックス名
  ,mainI.index_id --0:heap 1:clusterd >1:nonclustered
  ,mainI.type --0:heap 1:clustered 2:nonclustered >3:詳細は別途referenceを参照
  ,mainI.is_unique
  ,mainI.is_primary_key
  ,mainI.is_unique_constraint
  ,mainIC.index_column_id --インデックス内での固有のID
  ,mainIC.column_id --カラムと一対一に対応したID
  ,COL_NAME(mainIC.object_id, mainIC.column_id) as dup_column_name --重複カラム
  ,mainIC.key_ordinal --インデックス内でのキーの並び順
  ,mainIC.is_included_column --1:付加列 0:付加列じゃない
  ,mainIC.is_descending_key --1:降順 0:昇順
  ,mainPS.reserved_page_count * 8.0 / 1024 as 'Size(MB)'
FROM
  sys.objects AS mainO WITH (NOLOCK)
JOIN sys.indexes AS mainI WITH (NOLOCK) ON mainO.object_id = mainI.object_id
JOIN sys.index_columns AS mainIC ON mainI.object_id = mainIC.object_id AND mainI.index_id = mainIC.index_id
LEFT JOIN sys.dm_db_partition_stats AS mainPS ON mainPS.object_id = mainI.object_id and mainPS.index_id = mainI.index_id
WHERE
EXISTS (
  SELECT
    *
  FROM
  (
    SELECT
      o.name as tableName --テーブル名
      ,o.object_id as tableID
      ,i.name as indexName --インデックス名
      ,i.index_id --0:heap 1:clusterd >1:nonclustered
      ,i.type --0:heap 1:clustered 2:nonclustered >3:詳細は別途referenceを参照
      ,i.is_unique
      ,i.is_primary_key
      ,i.is_unique_constraint
      ,ic.index_column_id --インデックス内での固有のID
      ,ic.column_id --カラムと一対一に対応したID
      ,COL_NAME(ic.object_id, ic.column_id) as column_name
      ,ic.key_ordinal --インデックス内でのキーの並び順
      ,ic.is_included_column --1:付加列 0:付加列じゃない
      ,ic.is_descending_key --1:降順 0:昇順
    FROM
      sys.objects AS o WITH (NOLOCK)
    JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
    JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
    WHERE
      o.type = 'U'
    AND i.name NOT LIKE '_WA_Sys_%'
    --AND i.object_id = OBJECT_ID('テーブル名'); 
  ) AS A
  WHERE
    key_ordinal = 1 --第一列に限定
  GROUP BY
    tableID
    ,tableName
    ,column_id
  HAVING
    COUNT(*) > 1 --同一テーブル内で、第一列に同じカラムを指定したインデックスが複数あるものに限定
  AND tableID = mainO.object_id
  AND column_id = mainIC.column_id
)
AND key_ordinal = 1
AND (mainPS.reserved_page_count * 8.0 / 1024) > 500 --500MB以上のものに限定
ORDER BY
  mainO.name
  ,mainI.index_id
  ,mainIC.column_id

単純にテーブル、インデックス、インデックス内のカラム一覧をリストアップするクエリも需要あるかなと思いまして、のせておきます。

-- テーブル、インデックス、インデックス内のカラム一覧
SELECT
  o.name --テーブル名
  ,o.object_id
  ,i.name --インデックス名
  ,i.index_id --0:heap 1:clusterd >1:nonclustered
  ,i.type --0:heap 1:clustered 2:nonclustered >3:詳細は別途referenceを参照
  ,i.is_unique
  ,i.is_primary_key
  ,i.is_unique_constraint
  ,ic.index_column_id --インデックス内での固有のID
  ,ic.column_id --カラムと一対一に対応したID
  ,COL_NAME(ic.object_id, ic.column_id) as column_name
  ,ic.key_ordinal --インデックス内でのキーの並び順
  ,ic.is_included_column --1:付加列 0:付加列じゃない
  ,ic.is_descending_key --1:降順 0:昇順
FROM
  sys.objects AS o WITH (NOLOCK)
JOIN sys.indexes AS i WITH (NOLOCK) ON o.object_id = i.object_id
JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE
  o.type = 'U'
AND i.name NOT LIKE '_WA_Sys_%'
--AND i.object_id = OBJECT_ID('テーブル名'); 
ORDER BY
  o.name
  ,i.index_id
  ,ic.index_column_id