使用dm_exec_describe_first_result_set时表变量触发CREATE_TABLE触发器问题

Great question! Let's break down this counterintuitive behavior step by step:

1. Regular table variable declarations don't trigger the CREATE_TABLE trigger (as expected) #

First, let's confirm the baseline: when you run a plain DECLARE @A TABLE (b int) in a standard query window, it does NOT fire your database-level AFTER CREATE_TABLE trigger. That's because table variables are lightweight, session-scoped objects, and SQL Server doesn't raise a database-level CREATE_TABLE event for them during normal execution.

2. How sys.dm_exec_describe_first_result_set changes the context #

The sys.dm_exec_describe_first_result_set DMV isn't just parsing your query text—it runs through the full query compilation process to extract result set metadata. During this compilation phase, SQL Server needs to resolve all objects referenced in the query, including table variables.

Here's the critical quirk: in the DMV's internal metadata resolution flow, the act of resolving the table variable raises a database-level CREATE_TABLE event. This is a behavior unique to the DMV's compilation context—even though table variables aren't permanent database objects, the DMV's processing triggers the event your database-level listener is watching for.

3. The error chain that follows #

Once your CreateObjectDatabaseTrigger fires, it executes SP1, which creates a temp table #A, inserts data into it, then drops it. This is where the failure occurs:

• The sys.dm_exec_describe_first_result_set DMV depends on being able to fully resolve all metadata in the query and any associated code paths (like triggers that fire during its compilation step).
• Temp tables are strictly session-scoped, and their metadata isn't accessible during the DMV's metadata resolution phase. When the DMV encounters the INSERT INTO #A(b) VALUES(1) statement in SP1, it can't determine the temp table's metadata, leading to the exact error you saw:

  The metadata could not be determined because statement 'INSERT INTO #A(b) VALUES(1)' in procedure 'SP1' uses a temp table.

Key takeaway #

The root issue is that the DMV's compilation/metadata resolution process behaves differently than a regular query execution. It raises database-level events that wouldn't trigger during normal table variable usage, pulling in your trigger and its temp table-dependent logic—something the DMV isn't designed to handle.

内容的提问来源于stack exchange，提问作者Martin