如何获取存储过程中引用的所有列的列表? [英] How can I get a list of all columns referenced in a stored procedure?

问题描述

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

I have a number of stored procedures which use CTEs, temp tables, table variables, and sub queries and I need to get the list of all columns (including database, schema, and table/view) used in the stored procedure. I do not need to get the columns in the temp tables, table variables, or CTEs. I just need the referenced columns which are defined in a table or view in a database on my server.

我尝试了 sys.dm_sql_referenced_entities和 sys.sql_expression_dependencies 但他们没有在第一个选择查询或在 CTE 中选择后返回列.

I tried sys.dm_sql_referenced_entities and sys.sql_expression_dependencies but they do not return columns after the first select query or selected in a CTE.

推荐答案

当一个存储过程被执行时，它被解析并编译成一个查询计划，它被缓存，你可以通过 sys.dm_exec_cached_plans 和 sys.dm_exec_query_plan 访问它XML 格式.查询计划记录了解析代码的每个部分的输出列表".查看存储过程使用了哪些列只是查询此 XML 的问题，如下所示:

When a stored procedure is executed it is parsed and compiled into a query plan, this is cached and you can access it via sys.dm_exec_cached_plans and sys.dm_exec_query_plan in XML format. The query plan records the 'output list' of each section of the parsed code. Seeing which columns are used by the stored procedure is just a matter of querying this XML, like this:

--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')

Caveat emptor 这取决于您如何定义已使用".可能是您的存储过程中的 CTE 引用了表中的 5 列，但是当使用此 CTE 时，仅传递了其中的三列.查询优化器可能忽略这些额外的字段，而不将它们包含在计划中.另一方面，优化器可能会决定它可以通过在输出中包含额外的字段来进行更有效的查询，以便稍后使用更好的索引.此代码将返回查询计划使用的列，它们可能不完全是存储过程代码中的列.

Caveat emptor this depends on how you define 'used'. It may be that a CTE within your stored procedure references 5 columns from a table, but then when this CTE is used only three of the columns are passed on. The query optimizer may ignore these extra fields and not include them in the plan. On the flip side the optimizer may decide that it can make a more efficient query by including extra fields in an output to enable it to use a better index later on. This code will return the columns used by the query plan, they may not exactly be the columns that are in the stored procedure code.

这篇关于如何获取存储过程中引用的所有列的列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！