Dynamic SQL and Object Dependency

One of the most powerful features in MSSQL SERVER is dynamic sql that has its own complexity. When we use this feature, it’s very important that we must know its limitations and correct usage of it. By design, we limit in identify dependencies that invoked from Dynamic SQL.

Sample:

--drop proc if exists
IF OBJECT_ID('dbo.proc_1') > 0
DROP PROC dbo.proc_1 ;
go
--create proc_1
CREATE PROC dbo.proc_1
AS
DECLARE @sql NVARCHAR(MAX) = 'exec dbo.proc_2;' ;
--first call with sp_executesql (standard call mode)
EXEC sp_executesql @sql ;
--secound call with exec() (legacy call mode)
EXEC (@sql) ;
GO
--drop proc if exists
IF OBJECT_ID('dbo.proc_2') > 0
DROP PROC dbo.proc_2 ;
GO
--create proc_2
CREATE PROC dbo.proc_2
AS
SELECT 2 AS proc_2 ;
GO

Now check these stored procedures with sp_depends or from SSMS. Even sp_refreshsqlmodule cannot help us.


--refresh sql module for proc_1
EXEC sp_refreshsqlmodule 'proc_1';
-- call sp_depends for proc_1
EXEC sp_depends 'proc_1';
--refresh sql module for proc_2
EXEC sp_refreshsqlmodule 'proc_2';
-- call sp_depends for proc_2
EXEC sp_depends 'proc_2';

Your result must be like this: