时间戳

首页 > 解决方案 > T-SQL：我可以让 APPLY 在 CTE 上运行 TVF 吗？

sql - T-SQL：我可以让 APPLY 在 CTE 上运行 TVF 吗？

问题描述

我正在为存储过程的工作编写一些文档。我能够创建一个列出存储过程使用的表和列的查询，但这是单数的，需要我将存储过程的名称作为参数传递。我在 SQL 文档中遇到了 APPLY，听起来它可以做我想做的事。

下面的代码似乎已经完成了一半；如果您将子查询t替换n.name为任何存储过程的名称，您将获得我想要的一个存储过程的输出。

with sprocnames as
(
select distinct name from sys.procedures
where name like '%sp%'
)
SELECT *
FROM sprocnames n outer apply
(
SELECT
      n.name as SprocName
    , referenced_id
    , referenced_entity_name AS table_name
    , referenced_minor_name as column_name
    , is_all_columns_found
FROM sys.dm_sql_referenced_entities (n.name, 'OBJECT')
) as t
order by t.SprocName asc

所需的结果将是一个 SELECT，其中包含服务器上的每个存储过程以及每个使用的表和列的列表。

标签： sqltsql

解决方案

你很亲密。事实上，如此接近，当您发现您的代码缺少什么时，您可能会面面相觑：模式名称。

您提供的名称sys.dm_sql_referenced_entities应该是模式绑定的 - 即dbo.procedureName。

这是一个工作版本：

WITH CTE AS
(
    SELECT s.name +'.'+ n.name As FullName
    FROM sys.procedures n
    JOIN sys.schemas s 
        ON n.schema_id = s.schema_id 
    WHERE s.name LIKE '%sp%'
)

SELECT *
FROM CTE 
CROSS APPLY (
SELECT
      CTE.FullName as SprocName
    , referenced_id
    , referenced_entity_name AS table_name
    , referenced_minor_name as column_name
    , is_all_columns_found
FROM sys.dm_sql_referenced_entities (CTE.FullName, 'OBJECT')
) t

但是请注意，这也可能返回错误。事实上，当我在我的一个数据库上运行它时，我得到了以下错误（以及超过 900 行）：

消息 2020，级别 16，状态 1，第 4 行为实体“dbo.sp_upgraddiagrams”报告的依赖关系可能不包括对所有列的引用。这要么是因为实体引用了一个不存在的对象，要么是因为实体中的一个或多个语句中的错误。在重新运行查询之前，请确保实体中没有错误并且该实体引用的所有对象都存在。

这让我想到了最后一点：您不应该sp_为存储过程使用前缀。此前缀由 Microsoft 保留用于内置过程。
有关更多信息，请阅读 Aaron Bertrand 的“sp_ 前缀是否仍然是禁忌？

推荐阅读