SQL Server では sp_helptext というオブジェクトの定義を取得するためのストアドプロシージャが提供されています。類似の機能としては次のようなものも活用することができます。
私は sp_helptext を使用することが多いのですが、このストアドプロシージャでは一部の情報を見ることができません。
例としては、変更データキャプチャ を有効にする際の sys.sp_cdc_enable_db 内の処理があります。
EXEC sp_helptext 'sys.sp_cdc_enable_db'
でストアドプロシージャの内容を確認することができます。
このストアドプロシージャの中では「sys.sp_cdc_enable_db_internal」が呼び出されています。
このストアドプロシージャの内容を次のストアドプロシージャで確認しようとしてみます。
EXEC sp_helptext 'sys.sp_cdc_enable_db_internal'
そうすると次のようなメッセージが表示され、内容を確認することはできません。
システムストアドプロシージャの中ではこのように sp_helptext では解決することができないストアドプロシージャが呼び出されていることがあります。
このようなストアドプロシージャは内容が一切確認できないわけではありません。
DAC (専用管理者接続) で接続し、OBJECT_DEFINITION で解決をすることで、内容を確認できるケースがあります。
sys.sp_cdc_enable_db_internal であれば、DAC で接続したセッションから、次のクエリを実行することで定義を確認できます。
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_cdc_enable_db_internal'))
システムストアドプロシージャ内で呼び出されているシステムストアドプロシージャ内で、トランザクションが設定されていることがあり、ストアドの実行時にエラーとなった場合に、トランザクションがオープンされた状態となり、一部のシステムテーブルにロックがかかったままとなってしまい、他のトランザクションの実行が妨害されるケースがあります。
私は、そのような状態が発生するかどうかの確認を OBJECT_DEFINITION で内容を表示して確認することがあります。
SSMS でクエリを実行するのであれば「SET XACT_ABORT ON」を実行しておき、エラー時に自動的なロールバックを実行するようにしておくことで、システムストアドプロシージャエラー時のトランザクションの状態を制御することもできると思いますが、どのような処理が実行されるかを確認するための方法として、DAC + OBJECT_DEFINITION の活用も覚えておくとよいのではないでしょうか。