sql-server - 递归识别对象依赖关系的查询

Question

我有一个复杂的查询，其中包含多个表、 View 和函数。函数和 View split 成更多的 View 和函数，而这些 View 和函数又可能在其中 split 成更多的 View 和函数。

此查询存在性能问题，因此我希望获得查询中引用的所有对象的清晰简洁的列表，以便为我的调查奠定基础。如何获取此对象列表？

Answer 1

描述

编写此存储过程，在其下面递归地列出所有依赖子对象以及子对象的依赖对象和子对象的子对象...等。输入参数可以是Stored Proc、User Function、View。可以轻松更改以获得第 5 列的唯一列表，无论对象被调用的级别以及深度和由哪个对象调用。

列

1. UsedByObjectId - 使用依赖对象的父对象
2. UsedByObjectName - 父对象的名称
3. UsedByObjectType - 父对象的类型(P、V、FN)
4. DependentObjectId - 父对象使用的子对象
5. DependentObjectName - 子对象的名称
6. DependentObjectType - 依赖子对象的类型(P、V、FN、U)
7. 级别 - 对象使用的嵌套递归级别有多深

代码

--=========================================================================
--=========================================================================
--== utlGetAllDependentObjectsRecursive - Uses recursive common table
--==     expression to recursively get all the dependent objects as well
--==     as the child objects and child's child objects of a 
--==     Stored Procedure or View or Function.  can be easily modified to 
--==     include all other types of Objects
--=========================================================================
--=========================================================================
CREATE PROCEDURE utlGetAllDependentObjectsRecursive
(
   -- Supports Stored Proc, View, User Function, User Table
   @PARAM_OBJECT_NAME VARCHAR(500)
)
AS
BEGIN
    WITH CTE_DependentObjects AS
    (
        SELECT DISTINCT 
        b.object_id AS UsedByObjectId, 
        b.name AS UsedByObjectName, b.type AS UsedByObjectType, 
        c.object_id AS DependentObjectId, 
        c.name AS DependentObjectName , c.type AS DependenObjectType
        FROM  sys.sysdepends a
        INNER JOIN sys.objects b ON a.id = b.object_id
        INNER JOIN sys.objects c ON a.depid = c.object_id
        WHERE b.type IN ('P','V', 'FN') AND c.type IN ('U', 'P', 'V', 'FN') 
    ),
    CTE_DependentObjects2 AS
    (
       SELECT 
          UsedByObjectId, UsedByObjectName, UsedByObjectType,
          DependentObjectId, DependentObjectName, DependenObjectType, 
          1 AS Level
       FROM CTE_DependentObjects a
       WHERE a.UsedByObjectName = @PARAM_OBJECT_NAME
       UNION ALL 
       SELECT 
          a.UsedByObjectId, a.UsedByObjectName, a.UsedByObjectType,
          a.DependentObjectId, a.DependentObjectName, a.DependenObjectType, 
          (b.Level + 1) AS Level
       FROM CTE_DependentObjects a
       INNER JOIN  CTE_DependentObjects2 b 
          ON a.UsedByObjectName = b.DependentObjectName
    )
    SELECT DISTINCT * FROM CTE_DependentObjects2 
    ORDER BY Level, DependentObjectName    
END