Is there any reasonable way to capture the dependencies of a runtime object in SQL Server?
For example, apply this dynamic SQL script:
DECLARE @table SYSNAME = 'SomeTable'
DECLARE @column SYSNAME = 'SomeColumn'
DECLARE @proc SYSNAME
DECLARE @command NVARCHAR(MAX) = 'SELECT TOP 1 @proc = '+@column+' FROM '+@table
EXEC sp_executesql @command, N'@proc SYSNAME OUTPUT', @proc OUTPUT
EXEC @proc
Costs will runtime SomeTable, sp_executesqlvalue @procand any objects that are referenced during execution procedure @proc.
The methods that I have reviewed so far are:
capture the xml query plan from sys.dm_exec_query_plan from within each batch and transfer it to another process through the service broker for processing. Pros: I think this might work. Cons: Potentially expensive and intrusive: Each package and level of implementation should be further developed to capture the query plan.
. : , ! cons: , " ", , / / exec .. ..
:
DECLARE @guid UNIQUEIDENTIFIER
EXEC usp_begin_object_capture @guid OUTPUT
DECLARE @table SYSNAME = 'SomeTable'
DECLARE @column SYSNAME = 'SomeColumn'
DECLARE @proc SYSNAME
DECLARE @command NVARCHAR(MAX) = 'SELECT TOP 1 @proc = '+@column+' FROM '+@table
EXEC sp_executesql @command, N'@proc SYSNAME OUTPUT', @proc OUTPUT
EXEC @proc
EXEC usp_stop_object_capture @guid
SELECT object_name FROM object_capture_table WHERE guid = @guid
------------------------------
object_name
------------------------------
SomeTable
sp_executesql
<proc_named_by_@proc>
<object1_referenced_by_@proc>
<object2_referenced_by_@proc>
<object3_referenced_by_@proc>
<objectn_referenced_by_@proc>
:
/memoize . . , . - , , .
, . - .
? .