title: Capturing object dependencies at execution time in SQL Server
body: <p>Is there any sane way to capture execution-time object dependencies in SQL Server?</p>
  
  <p>For instance, take this dynamic SQL scenario:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>The execution-time dependencies would be <code>SomeTable</code>, <code>sp_executesql</code>, the value of <code>@proc</code>, and whatever objects are referenced <em>at run-time</em> by the @proc procedure.</p>
  
  <p>Methods I have considered thus far:</p>
  
  <ul>
  <li><p>grabbing the xml query plan from sys.dm_exec_query_plan from within each batch, and passing it off to another process via service broker for processing. pros: I think it might actually work. cons: potentially expensive, and intrusive: each batch and execution level must be retrofitted to capture the query plan.</p></li>
  <li><p>extended events. pros: if it could be made to work, then great! cons: I don't think there is a suitable event class for "object access", eg scan and/or seek and/or exec etc etc.</p></li>
  </ul>
  
  <p>Ideally, the capture would work something like this:</p>
  
  <pre><code>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
  &lt;proc_named_by_@proc&gt;
  &lt;object1_referenced_by_@proc&gt;
  &lt;object2_referenced_by_@proc&gt;
  &lt;object3_referenced_by_@proc&gt;
  &lt;objectn_referenced_by_@proc&gt;
  </code></pre>
  
  <p>Context: </p>
  
  <p>I am trying to cache/memoize the deterministic result sets of long-running procedures. The underlying data is fairly static. If I could gather actual dependencies at execution-time, I could automatically associate an entry in the cache with a set of objects. If any of those objects changed, I would know which entries to invalidate.</p>
  
  <p>This may be a poor caching strategy, I don't know. But the dependency technique would still be useful in other contexts.</p>
  
  <p>Any thoughts? Many thanks.</p>