SQLServer: ストアドプロシージャ内で参照しているストアドプロシージャを抽出するときにハマったこと

4984 ワード
SQLServer SQLServer テキストリンク

背景

sys.sql_expression_dependenciesというテーブルを使うと、特定のストアドプロシージャで参照しているテーブルや、EXECUTEしているストアドプロシージャを自動で抽出することができます。

以下のクエリを実行することで、ストアドプロシージャ内で実行されているストアドプロシージャをリストアップできるかと試してみたのですが、うまくいきませんでした。

select *
from sys.sql_expression_dependencies as sd
inner join sys.objects as o on o.object_id = sd.referenced_id
    and type = 'P'

データを確認すると、referenced_idがNULLになっていたので、本挙動について調査してみました。

調査結果

ドキュメントをみると、referenced_idは

参照先エンティティの ID。 スキーマバインド参照の場合、この列の値は NULL になりません。

となっています。
「スキーマバインド」の意味をとりかねたのですが、おそらく「スキーマ指定までしているかどうか」だと思い、以下の実験をしてみました。

create proc proc111
as
begin
    select 1
end
go

--スキーマバインドなし
create proc proc222
as
begin
    exec proc111
end
go

--スキーマバインドあり
create proc proc333
as
begin
    exec dbo.proc111
end

スキーマバインド無しの場合の依存関係抽出結果

select *
from sys.sql_expression_dependencies as sd
where referencing_id = object_id('proc222')

スキーマバインドありの場合の依存関係抽出結果

select *
from sys.sql_expression_dependencies as sd
where referencing_id = object_id('proc333')

スキーマバインドありだと、referenced_idが取得できました。

スキーマバインド無しのストアドプロシージャを参照している場合、sys.sql_expression_dependenciesを使用するときは、referenced_idの代わりにreferenced_entity_nameを使用するしかなさそうです。

※sql_dependenciesであれば取得できるようですが、非推奨のテーブルとなっています。