背景
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であれば取得できるようですが、非推奨のテーブルとなっています。