SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server で sp_helptext では参照できないオブジェクトのテキストを取得する

leave a comment

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」が呼び出されています。

image

このストアドプロシージャの内容を次のストアドプロシージャで確認しようとしてみます。

EXEC sp_helptext 'sys.sp_cdc_enable_db_internal'

そうすると次のようなメッセージが表示され、内容を確認することはできません。

image

システムストアドプロシージャの中ではこのように sp_helptext では解決することができないストアドプロシージャが呼び出されていることがあります。

このようなストアドプロシージャは内容が一切確認できないわけではありません。

DAC (専用管理者接続) で接続し、OBJECT_DEFINITION で解決をすることで、内容を確認できるケースがあります。

sys.sp_cdc_enable_db_internal であれば、DAC で接続したセッションから、次のクエリを実行することで定義を確認できます。

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sp_cdc_enable_db_internal'))

image

システムストアドプロシージャ内で呼び出されているシステムストアドプロシージャ内で、トランザクションが設定されていることがあり、ストアドの実行時にエラーとなった場合に、トランザクションがオープンされた状態となり、一部のシステムテーブルにロックがかかったままとなってしまい、他のトランザクションの実行が妨害されるケースがあります。

私は、そのような状態が発生するかどうかの確認を OBJECT_DEFINITION で内容を表示して確認することがあります。

SSMS でクエリを実行するのであれば「SET XACT_ABORT ON」を実行しておき、エラー時に自動的なロールバックを実行するようにしておくことで、システムストアドプロシージャエラー時のトランザクションの状態を制御することもできると思いますが、どのような処理が実行されるかを確認するための方法として、DAC + OBJECT_DEFINITION の活用も覚えておくとよいのではないでしょうか。

Share

Written by Masayuki.Ozawa

4月 20th, 2022 at 9:59 am

Posted in SQL Server

Tagged with

Leave a Reply