存储过程中引用的所有列表如何获取？

孙必海

我有很多用途CTE，在临时表、表变量和子查询的存储过程中，我需要获取存储过程中使用的所有列（包括数据库、架构和表/视图）的列表。我不需要获得临时表、表变量或CTE中列。我只需要在服务器上的数据库表或视图中定义引用列。
我试过了sys.dm_sql_referenced_entities，sys.sql_expression_dependencies但是，在第一次选择查询之后CTE选择后，它们不返回列。
/ r; H0 y6 @3 @  x                                                               
6 a5 X8 q" C' _8 J- D    解决方案:                                                               
                                                                当执行存储过程时，它将被分析并编译成查询计划并缓存，您可以通过sys.dm_exec_cached_plans和sys.dm_exec_query_plan以XML访问格式。查询计划记录分析代码各部分的输出列表。查看存储过程中使用了哪些列，只要查看此XML这样的问题：
--Execute the stored procedure to put its query plan in the cacheexec sys.sp_columns ''DECLARE @TargetObject nvarchar(100) = 'sys.sp_columns';WITH XMLNAMESPACES (    'http://schemas.microsoft.com/sqlserver/2004/07/showplan' as ns1),CompiledPlan AS  SELECT         (SELECT query_plan FROM sys.dm_exec_query_plan(cp.plan_handle)) qp,       (SELECT ObjectID FROM sys.dm_exec_sql_text(cp.plan_handle)) ob    FROM sys.dm_exec_cached_plans cp    WHERE objtype = 'Proc'),ColumnReferences AS  SELECT DISTINCT        ob,       p.query('.').value('./ns1:ColumnReference[1]/@Database','sysname') AS [Database],       p.query('.').value('./ns1:ColumnReference[1]/@Schema','sysname') AS [Schema],       p.query('.').value('./ns1:ColumnReference[1]/@Table','sysname') AS [Table],       p.query('.').value('./ns1:ColumnReference[1]/@Column','sysname') AS [Column]    FROM CompiledPlan        CROSS APPLY qp.nodes('//ns1:ColumnReference') t(p))SELECT     [Database],                [Schema],    [Table],    [Column]FROM ColumnReferences WHERE     [Database] IS NOT NULL AND     ob = OBJECT_ID(@TargetObject,'P')请注意，    这取决于您如何定义已使用。您的存储过程CTE表中的5列可能被引用，但当使用时CTE只有三列传输。查询优化器 可能会
+ q5 W' k# ^& e3 ^7 l- n2 I忽略这些额外的字段，而不包括在计划中。另一方面，优化器可以更有效地查询输出中包含的额外字段，以便将来使用更好的索引。该代码将返回查询计划中使用的列，它们可能不完全是存储过程中的列。