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

Question

我有许多使用CTE，临时表，表变量和子查询的存储过程，我需要获取该存储过程中使用的所有列（包括数据库，架构和表/视图）的列表。我不需要获取临时表，表变量或CTE中的列。我只需要在服务器上的数据库的表或视图中定义的引用列。

我试过了sys.dm_sql_referenced_entities，sys.sql_expression_dependencies但是在第一个选择查询后或在CTE中选择后，它们不返回列。

Answer 1

当执行存储过程时，它将被解析并编译成查询计划，将对其进行缓存，您可以通过sys.dm_exec_cached_plans和sys.dm_exec_query_plan以XML格式对其进行访问。查询计划记录已解析代码的每个部分的“输出列表”。查看存储过程使用了哪些列，只是查询此XML的问题，就像这样：

--Execute the stored procedure to put its query plan in the cache
exec 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时，仅传递了三列。查询优化器 可能会
忽略这些额外的字段，并且不将它们包括在计划中。另一方面，优化器可以通过在输出中包括额外的字段来决定它可以进行更有效的查询，以便以后可以使用更好的索引。此代码将返回查询计划使用的列，它们可能不完全是存储过程代码中的列。