MENU

Azure SQL Databaseで未使用列を洗い出す方法｜ストアド/ビュー未参照列の抽出と安全な削除手順

Azure

Azure SQL Databaseでテーブルの列が膨大になると、「どの列が本当に使われているのか」が分からず、性能・運用・コストの全てが重くなります。本記事では、ストアドプロシージャ／ビューから参照されない列を静的解析で候補抽出し、Query Storeなどの実行実績で裏取りして安全に整理する手順を解説します。

目次

Azure SQL Databaseで「未使用列」を洗い出すのが難しい理由

「未使用列」と一言で言っても、SQL Server（Azure SQL Databaseを含む）では“何をもって未使用とみなすか”で難易度が大きく変わります。特に列が6.5万列以上といった規模になると、単純な検索や目視では破綻します。

  • 静的解析（定義ベース）：ストアドプロシージャ（SP）やビュー（View）の定義上、列名が参照されていないか
  • 実行実績（ログ／統計ベース）：実際に実行されたSQLの中で、その列が使われた痕跡があるか
  • 利用者（アプリ／帳票／連携）ベース：列を返していても、最終利用者がその列を参照しているか

このうち、「SP/ビューで一切使われない列」をDB内だけで100%断定するのは現実的に難しいケースがあります。代表例が次の2つです。

  • 動的SQL（文字列でSQLを組み立ててEXECする）：依存関係ビューや関数が追えない（追えたとしても限定的）
  • SELECT *：列名が明示されないため、列レベル依存関係が取りづらく、定義検索も効きにくい

そこで現場では、「候補を作る静的解析」→「裏取りの実行実績」→「安全な削除手順」の二段（＋運用）で進めるのが最も失敗しづらいです。

最初に決めるべき「未使用」の定義

列削除の判断を誤ると、アプリの障害や帳票崩れ、ETL失敗などに直結します。最初に、どのレベルの“未使用”を狙うかを合意しておくと手戻りが減ります。

未使用の定義判定のしやすさ主な根拠注意点
SP/ビューの定義上参照されない（静的）中依存関係DMV、モジュール定義解析動的SQL・SELECT *・アドホックSQLは拾えない
実行されたSQLにも出てこない（実行実績）中〜高Query Store、監査ログ、Extended Events観測期間が短いと「たまたま使われなかった」だけになる
利用者がいない（業務的に廃止）低（人が必要）仕様、帳票、連携一覧、オーナー確認関係者が把握していない“隠れ利用”がある

この記事の中心は「SP/ビューで参照されない列」を見つけることですが、削除の最終判断には必ず実行実績や業務面の裏取りを組み合わせます。

全体の進め方：候補抽出→裏取り→段階的に外す

列数が多い環境ほど、最初から“完全な正解”を狙うより、誤判定を織り込みつつスコアリングして絞り込む方が現実的です。

フェーズ目的成果物ポイント
静的解析「定義上参照されない列」を候補化未参照候補リスト（テーブル・列）誤判定前提。SELECT * と動的SQLを別枠扱い
実行実績の裏取り候補が本当に使われていないか確認Query Store/ログの証跡、頻度観測期間はバッチ周期を跨ぐ。月次があるなら最低1〜2ヶ月など
データ状況の確認“不要そう”の根拠を強化NULL率、値の偏り、更新頻度の推定NULL率が高くても「将来用途」や「エラー時の退避」に使う例がある
段階的な廃止影響を最小化して撤去非推奨化→停止→DROPいきなりDROPしない。戻せる状態で進める

静的解析で「定義上参照されない列」を候補抽出する

静的解析は、SQLモジュール（SP/ビュー/関数など）の定義から参照列を拾い、「全列」から差分を取って候補を作る発想です。精度100%は難しいものの、列が膨大な環境では最も効果が出ます。

依存関係 + モジュール定義検索を組み合わせる（簡易）

受け入れ回答としてよく紹介されるのが、sys.sql_expression_dependencies（依存関係）と、OBJECT_DEFINITION（モジュール定義）を使った“列名が出てこない列”の抽出です。

SELECT
  table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE NOT EXISTS
(
  SELECT *
  FROM sys.sql_expression_dependencies d
  WHERE OBJECT_NAME(d.referenced_id) = c.table_name
    AND OBJECT_DEFINITION(d.referencing_id) LIKE '%' + c.column_name + '%'
)
ORDER BY 1, 2;

この形は「とりあえず候補を一気に出す」には便利ですが、実運用では次の理由で誤判定が出やすいです。

  • 列名が短い（例：Id、No、Cd）と部分一致で拾ってしまう／逆に誤って除外してしまう
  • スキーマ名を見ていないため、同名テーブルがあると混線する
  • SELECT *は列名が出ないので「未参照」に見えやすい（ただし結果セットとしては返している可能性）
  • 動的SQL、アプリからのアドホックSQL（直接投げ）は基本拾えない

そのため、このクエリは“一次スクリーニング（候補抽出）”と割り切り、次の改善や補助を入れるのが実務的です。

誤判定を減らすコツ（文字列検索の精度を上げる）

文字列検索は完全には避けられないことが多い一方で、工夫すると誤判定をかなり減らせます。

  • 角括弧やドット区切りを意識する：[Column]、.Column、Column, など“識別子っぽい形”を優先して探す
  • スキーマ付きテーブル名で対象モジュールを絞る：まず定義中に dbo.Table が出るモジュールだけを対象にする
  • コメント・文字列リテラルに列名が出てくるケースを想定する（完全対策は難しいが「候補」扱いにする）
  • 同名列が多い環境では、単純LIKEではなく“テーブル名＋列名の組”で当たりを付ける

列が6.5万列以上ある環境で「全モジュール × 全列」を総当たりすると現実的な時間で終わらないことがあります。そこで、テーブル単位で対象モジュールを絞ってから列を当てるとスケールしやすいです。

テーブル単位で候補を作る（スケール重視の考え方）

巨大スキーマでは、まず「このテーブル（またはこのスキーマ）を整理したい」という単位で進めるのが現実的です。以下は考え方の例です。

DECLARE @schema sysname = N'dbo';
DECLARE @table  sysname = N'YourBigTable';

;WITH target_table AS
(
    SELECT t.object_id,
           QUOTENAME(SCHEMA_NAME(t.schema_id)) + N'.' + QUOTENAME(t.name) AS full_table_name
    FROM sys.tables t
    WHERE t.name = @table AND SCHEMA_NAME(t.schema_id) = @schema
),
target_cols AS
(
    SELECT c.object_id,
           c.column_id,
           c.name AS column_name
    FROM sys.columns c
    JOIN target_table tt ON tt.object_id = c.object_id
),
modules AS
(
    SELECT o.object_id,
           QUOTENAME(SCHEMA_NAME(o.schema_id)) + N'.' + QUOTENAME(o.name) AS module_name,
           m.definition
    FROM sys.sql_modules m
    JOIN sys.objects o ON o.object_id = m.object_id
    CROSS JOIN target_table tt
    WHERE o.type IN ('P','V','FN','TF','IF')
      AND m.definition LIKE N'%' + tt.full_table_name + N'%'
)
SELECT tc.column_name
FROM target_cols tc
WHERE NOT EXISTS
(
    SELECT 1
    FROM modules m
    WHERE m.definition LIKE N'%' + QUOTENAME(tc.column_name) + N'%'
       OR m.definition LIKE N'%.' + tc.column_name + N'%'
);

このやり方は「そのテーブルが登場するモジュールだけ」を見に行くため、全体総当たりより現実的に回しやすい一方、もちろん文字列検索なので誤判定は残ります。ここで重要なのは、結果を“削除候補の最終リスト”ではなく、“調査キュー（棚卸し対象）”として扱うことです。

SELECT * をどう扱うか（ここが事故ポイント）

SELECT *が混在している環境では、「SP/ビューで参照されない列」を探すだけでは不十分です。なぜなら、列名は書かれていなくても、結果セットとして列を返している可能性があるからです。

  • SPやビューが SELECT * で列を返している場合、列をDROPすると返却列の形が変わり、クライアント（アプリ／ETL／BI）が壊れることがある
  • 依存関係DMVや定義検索では「列名が出ない＝未参照」と判定されがちだが、実際は“返している”

実務では、次のどちらかを先に行うのが安全です。

  • SP/ビューの SELECT * を列明示に置き換える（まずは“返す列”を固定化）
  • SELECT * を含むモジュールは「未使用判定の対象外」にして、別途リファクタリング計画に回す

列明示の置き換えに便利なのが、対象テーブルの列リスト生成です。

SELECT STRING_AGG(QUOTENAME(c.name), ', ') WITHIN GROUP (ORDER BY c.column_id) AS column_list
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'dbo.YourBigTable');

これで得た列リストをベースに、ビュー／SPのSELECTリストを明示化してから、未使用列の判定精度を上げていくのが王道です。

より構造的に参照列を拾う：sys.dm_sql_referenced_entities

文字列LIKEより“構造寄り”に拾いたい場合、sys.dm_sql_referenced_entitiesでモジュールの参照先（テーブル／列）を取得し、「参照されている列一覧」を作って差分を取る方法があります。

SELECT
    referenced_schema_name,
    referenced_entity_name,
    referenced_minor_name AS referenced_column_name
FROM sys.dm_sql_referenced_entities(N'dbo.usp_YourProcedure', N'OBJECT')
WHERE referenced_minor_name IS NOT NULL;

この方法のメリットは、列名の部分一致問題がかなり減る点です。一方で注意点もあります。

  • 動的SQLの中身までは追えない
  • SELECT * は列として展開されない（＝列名は返らないことがある）
  • 解析できないモジュール（定義が取得できない、特定の構文など）が混ざるとエラーになる場合がある

そのため、全モジュールを一括で処理する場合は、エラーを握りつぶさず「解析失敗モジュール」を別リストにして、“未使用候補の信頼度”を分ける設計が堅牢です。

実行実績で裏取りする：Query Store とDMVの使い分け

静的解析は「候補作り」に強い一方、最終的な安心材料としては弱いことがあります。そこでAzure SQL Databaseでは、Query StoreやDMV（動的管理ビュー）を使い、実行されたクエリの痕跡から裏取りします。

Query Storeで「実際に投げられているSQL」を確認する

Query Storeが有効なら、一定期間のクエリテキストや実行統計が残ります。静的解析で拾えないアプリ直叩き（アドホックSQL）の有力な手掛かりになります。

まず状態確認の例です。

SELECT actual_state_desc, readonly_reason, current_storage_size_mb, max_storage_size_mb
FROM sys.database_query_store_options;

次に、特定テーブルに触れているクエリを拾う例です（テーブル名表記ゆれがあるのでLIKE条件は調整してください）。

SELECT TOP (200)
    q.query_id,
    rs.last_execution_time,
    rs.count_executions,
    qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p
    ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs
    ON rs.plan_id = p.plan_id
WHERE qt.query_sql_text LIKE N'%dbo.YourBigTable%'
   OR qt.query_sql_text LIKE N'%[dbo].[YourBigTable]%'
ORDER BY rs.last_execution_time DESC;

この結果から、

  • 該当テーブルを触るクエリの種類（SELECT/INSERT/UPDATE）
  • SELECTリストに列名が出ているか
  • 実行頻度と最終実行時刻

を把握できます。列単位での完全な機械判定は難しいものの、「候補列が本当に一度も登場していないか」の裏取りとしては非常に強力です。

特に月次や季節バッチがある環境では、Query Storeの観測期間が短いと「たまたま最近出ていないだけ」を未使用と誤判定します。業務サイクルを跨ぐ期間を確保してから判断するのが安全です。

sys.dm_db_index_usage_statsで「ほぼ参照されないテーブル／インデックス」を粗く絞る

別アプローチとして、sys.dm_db_index_usage_statsで“読まれていないインデックス／テーブル”を見つけることができます。ただしこれは列単位ではなく、テーブル／インデックス単位のヒントです。

SELECT
    OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
    OBJECT_NAME(s.object_id)        AS table_name,
    i.name                          AS index_name,
    s.user_seeks,
    s.user_scans,
    s.user_lookups,
    s.user_updates,
    s.last_user_seek,
    s.last_user_scan,
    s.last_user_lookup,
    s.last_user_update
FROM sys.dm_db_index_usage_stats AS s
JOIN sys.indexes AS i
  ON i.object_id = s.object_id
 AND i.index_id  = s.index_id
WHERE s.database_id = DB_ID()
ORDER BY (s.user_seeks + s.user_scans + s.user_lookups) ASC;

使いどころは次のような局面です。

  • 「ほぼ参照されないテーブル群」を先に見つけ、列整理の優先順位を付ける
  • 更新は多いのに読み取りがほぼ無いテーブルがあれば、設計（データ蓄積だけして使っていない）を疑う

注意点として、DMVの統計は環境要因（フェイルオーバーなど）でリセットされることがあります。数字だけで断定せず、Query Storeや業務ヒアリングと合わせて判断してください。

候補列の“不要っぽさ”を強化する：NULL率・値の偏りを確認する

静的解析で「未参照候補」に入っても、実際は将来用途の列、エラーハンドリング用の退避列、監査目的の列などで残していることがあります。そこで、データ状況の確認で“不要っぽさ”の根拠を増やします。

NULL率が極端に高い列を確認する

「NULLだらけ」の列は、未使用候補として優先度が上がります。ただし、NULL率が高い＝不要とは限らない点は押さえておきましょう（例：例外時だけ埋まる列、特定顧客だけ使う列）。

対象テーブルが決まっているなら、候補列だけを対象にしてNULL率を集計するのが現実的です。候補列が多い場合は、まず上位数十列に絞って回すと安全です。

-- 例：特定列のNULL率（まずは代表列から）
SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN SomeColumn IS NULL THEN 1 ELSE 0 END) AS null_rows,
    CAST(100.0 * SUM(CASE WHEN SomeColumn IS NULL THEN 1 ELSE 0 END) / NULLIF(COUNT(*), 0) AS decimal(5,2)) AS null_pct
FROM dbo.YourBigTable;

列数が膨大な場合、全列を一括集計する動的SQLは長大化しやすいので、

  • まず静的解析で候補を十分に絞る
  • 候補の中でも「命名が怪しい」「過去の追加列」「NULL率が高そう」などで優先順位を付ける

という順に進めるのが失敗しにくいです。

“更新されていない”の推定は難しい（代替の考え方）

列単位の更新回数を直接カウントできる仕組みは一般的にはありません。その代わりに、

  • 列が監査対象なら、更新日時・更新者の設計があるか
  • アプリがその列を埋めるはずの処理が存在するか
  • 例外時のみ埋まる列なら、障害時データやログを参照して確認できるか

など、設計・運用の証跡を合わせて判断します。

安全に削るための実務ステップ（失敗しない進め方）

「未参照っぽい」だけでDROPすると、あとから“隠れ利用”が見つかり事故になります。ここでは、現場で安全に進めるための手順を具体化します。

手順やることチェックポイント成果物
候補抽出依存関係 + モジュール解析で未参照候補を作るSELECT * を含むモジュールは別扱いにする候補列リスト（信頼度付き）
裏取りQuery Storeでテーブル／列が登場するクエリを確認観測期間が業務周期を跨いでいるか証跡（クエリID、最終実行時刻、頻度）
データ確認NULL率・値の偏り・例外時利用の有無を確認NULLでも意味がある列（例外時だけ）を除外優先度の再排序
段階的に外すまずは出力から外す／入力停止→一定期間監視アプリやETLのエラーログ監視をセットで廃止計画（移行期間・戻し手順）
最終削除ALTER TABLE DROP COLUMN を適用依存オブジェクトとデプロイ手順が揃っているかスキーマ簡素化、運用負荷低減

「いきなりDROPしない」を具体化する（安全策の例）

段階的に進めるための現実的な安全策をいくつか挙げます。

  • ビューやSPのSELECTリストから外す：まず“返さない”にして、利用者側でエラーが出ないか監視
  • 書き込みを止める：アプリやETL側でその列の更新を停止し、将来も埋まらない状態にする
  • 非推奨（Deprecated）として明示：列に拡張プロパティや命名規約（例：Deprecated_）で「削除予定」を可視化
  • 削除はリリース手順に載せる：DB変更だけで完結させず、アプリ・ETL・BIの担当と同時にリリース

列削除は“技術”というより“変更管理”の側面が強い作業です。特に列数が膨大な環境では、候補を出して終わりではなく、監視と巻き戻し前提で進めるのが成功パターンです。

よくある落とし穴と対策

列名が短い／汎用的で、誤判定が大量に出る

Id、Code、Nameのような列は、文字列検索では誤判定の温床です。対策としては、

  • テーブル名で対象モジュールを絞ったうえで検索する
  • [Column] のように角括弧付き、または Alias.Column の形を優先して探す
  • 静的解析の結果に信頼度（高/中/低）を付け、短い列名は低信頼に寄せる

動的SQLがあるせいで「未参照」に見える

動的SQLはDB外の知識（アプリ実装、SQL生成ルール）や、Query Store/ログの観測が重要です。静的解析だけでDROPすると事故率が上がります。動的SQLが多いシステムなら、

  • Query Storeで実行SQLを長めに観測
  • 重要テーブルに関しては、変更前後でエラーログ（SQL例外）を重点監視

をセットにしてください。

SELECT * が多く、列依存が追えない

SELECT * のまま列削除を進めると、列を使っていなくても「返却列が変わった」ことで壊れます。対策は、

  • まずSELECT * を列明示にリファクタリングする
  • それが難しい場合、SELECT * を含むモジュールが返す結果セットを“契約”として扱い、列削除の対象外にする

です。列整理より先に、結果セットの契約（列の固定化）を進めた方が、長期的な運用コストは下がります。

大規模スキーマを継続的に整理するための運用アイデア

6.5万列以上の環境では、一度の棚卸しだけではすぐに元に戻ります。継続的に「不要列が増えにくい」仕組みを入れると、次回以降の分析も楽になります。

  • 列追加のルール化：追加理由、利用箇所（SP/ビュー/アプリ）、廃止予定の有無を必須項目にする
  • スキーマ辞書（データ辞書）の整備：列の用途・オーナー・参照箇所・更新箇所を管理（最初は重要テーブルだけでも効果大）
  • Query Storeの運用：保持期間・容量・クリーニング方針を決め、棚卸しの“観測基盤”として使う
  • SELECT * 禁止（または抑制）：ビュー／SPは列明示を原則化し、将来の静的解析精度を上げる

列削除は一度きりの作業に見えますが、実際は品質改善のサイクルです。「候補抽出できる設計」「観測できる運用」「安全に外せる変更管理」を揃えると、スキーマは着実に軽くなります。

まとめ：未使用列は“候補抽出”と“裏取り”の二段構えが現実解

Azure SQL Databaseで「SP/ビューから参照されない列」を洗い出す場合、最初は静的解析で候補を作り、Query StoreやDMVで実行実績を確認してから段階的に外すのが安全です。特に動的SQLやSELECT * がある環境では、静的解析だけで断定せず、観測と変更管理をセットにして進めてください。

Azure
SQL Server ストアドプロシージャ Azure SQL Database Query Store 未使用列

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次