递归查询对象依赖关系的查询

Question

递归查询对象依赖关系的查询

sql-serverdependenciescommon-table-expression

8

我有一个复杂的查询，其中包含多个表、视图和函数。这些函数和视图又分裂成更多的视图和函数，而这些视图和函数中可能还包含更多的视图和函数。

由于这个查询存在性能问题，因此我想获得一个清晰而简明的列表，其中包含我的查询中引用的所有对象，以便我有一个基础进行调查。如何获取这个对象列表？

- Eric J. Price

6个回答

7

我看到了这篇文章，其中有一种方法可以列出所有引用特定同义词的对象，并使用答案中的基本逻辑在递归CTE中识别与正在执行的顶级查询中的逗号分隔的对象列表相关的所有对象。

Declare @baseObjects Nvarchar(1000) = '[Schema].[Table],[Schema].[View],[Schema].[Function],[Schema].[StoredProc]',
        @SQL Nvarchar(Max);

Declare @objects Table (SchemaName Varchar(512), TableName Varchar(512), ID Int, xtype Varchar(10));

Set     @SQL = 'Select  ss.name As SchemaName,
                        so.name As TableName,
                        so.id,
                        so.xtype
                From    sysobjects so
                Join    sys.schemas ss
                        On  so.uid = ss.schema_id
                Where   so.id In (Object_ID(''' + Replace(@baseObjects,',','''),Object_ID(''') + '''))';

Insert  @objects
Exec    sp_executeSQL @SQL;

With    test As
(
        Select  ss.name As SchemaName,
                so.name As TableName,
                so.id,
                so.xtype
        From    sys.sql_expression_dependencies sed
        Join    @objects vo
                On  sed.referencing_id = vo.ID
        Join    sysobjects so
                On  sed.referenced_id = so.id
        Join    sys.schemas ss
                On  so.uid = ss.schema_id
        Union   All
        Select  ss.name As SchemaName,
                so.name As TableName,
                so.id,
                so.xtype
        From    test
        Join    sys.sql_expression_dependencies sed
                On  sed.referencing_id = test.id
                And sed.referencing_id <> sed.referenced_id
        Join    sysobjects so
                On  sed. referenced_id = so.id
        Join    sys.schemas ss
                On  so.uid = ss.schema_id
)
Select  Distinct *
From    test
Union
Select  *
From    @objects;

- Eric J. Price

4

检查这个，您将获得所有递归对象。

    WITH Refobjects 
(referencing_object_name,referencing_object_type_desc) 
    AS 
    (
        SELECT
        o.name AS referencing_object_name,
        o.type_desc AS referencing_object_type_desc
        FROM
        sys.sql_expression_dependencies sed
        INNER JOIN
        sys.objects o ON sed.referencing_id = o.[object_id]
        WHERE
        sed.referenced_entity_name = 'Your Object Name'
      UNION ALL 

        SELECT
            o.name AS referencing_object_name,
            o.type_desc AS referencing_object_type_desc
        FROM
            sys.sql_expression_dependencies sed
            INNER JOIN
            sys.objects o ON sed.referencing_id = o.[object_id]
            INNER JOIN Refobjects ON sed.referenced_entity_name = Refobjects.referencing_object_name 
    )
    SELECT distinct * FROM Refobjects
    Order by 2 desc,1 ;

- Raju Chavan

1

Plus one（加一）- 简单优雅且非常实用。真是个谜为何还没有人点赞您。 - Reversed Engineer

3

在 SQL Server 2008 中，引入了两个新的动态管理函数来跟踪对象依赖关系：sys.dm_sql_referenced_entities 和 sys.dm_sql_referencing_entities。

1/ 返回引用给定实体的实体：

SELECT
        referencing_schema_name, referencing_entity_name, 
        referencing_class_desc, is_caller_dependent
FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT')

2/ 返回被对象引用的实体:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('<StoredProcedureName>', 'OBJECT');

另一种选择是使用一个非常实用的工具，称为SQL Dependency Tracker，来自Red Gate。

- bjnr

1

这是你的网站吗？Sophos目前阻止了链接，显示“所请求的位置包含恶意内容，被识别为Troj/JSRedir-RX并已被阻止下载。” 说实话，我本来期望他们会链接到相关的BOL条目。 - Martin Smith

这不是递归的，因此不能回答OP的问题。 - Reversed Engineer

0

基于@Raju Chavan上面的答案，它非常有效。然而...

我已经添加了对模式的支持，并返回（并按）递归级别排序，因此可以轻松将其转换为脚本以按正确顺序刷新引用对象，使用sp_refreshsqlmodule（请参见下面的第3点）。

WITH 
  cRefobjects AS (

      SELECT o.name, s.name AS sch, o.type_desc, 1 AS level
      FROM sys.sql_expression_dependencies sed
        INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
      WHERE (sed.referenced_schema_name = '<your schema>' OR sed.referenced_schema_name IS NULL)
      AND  sed.referenced_entity_name = '<your object name>'

    UNION ALL 

      SELECT o.name, s.name AS sch, o.type_desc, cRefobjects.level + 1 AS level
      FROM
        sys.sql_expression_dependencies AS sed
        INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
        INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
        INNER JOIN cRefobjects ON sed.referenced_entity_name = cRefobjects.name
                               AND sed.referenced_schema_name = cRefobjects.sch
  )

SELECT DISTINCT name, sch, type_desc, level 
FROM cRefobjects
ORDER BY level, type_desc DESC, name;

需要考虑以下几点：

将 <your schema> 和 <your object> 替换为您所需的内容。
当引用对象在引用对象中没有模式前缀时，模式实际上是未知的，因此谓词上方有 OR sed.referenced_schema_name IS NULL。如果您的数据库对象不遵循最佳实践，则可能会获取错误的引用对象。
我寻找并找到这个答案的目标是编写一个脚本，在编辑视图后自动刷新数据库中的引用对象，使用 sp_refreshsqlmodule。为此，只需将上面显示的 CTE 包装如下。这将打印刷新引用对象所需的 SQL，并按正确顺序进行：

DECLARE @SQL NVARCHAR(4000); SET @SQL = '';
WITH 
  cRefobjects AS (
    ...
  )
--SELECT DISTINCT name, sch, type_desc, level 
SELECT @SQL = @SQL + 'EXEC sys.sp_refreshsqlmodule ''' + sch + '.' + name + '''' + CHAR(13)+CHAR(10)
FROM cRefobjects
ORDER BY level, type_desc DESC, name;

PRINT @SQL

- Reversed Engineer

0

我改进了以上答案，因为它们都对我无效。

我需要一种通过类型 sp_refreshsqlmodule 刷新复杂嵌套对象的方法。

您需要将 <TYPE_NAME> 更新为自己的。

以下方法适用于我：

WITH cRefobjects AS (

  SELECT o.name, s.name AS sch, o.type, 1 AS level
  FROM sys.sql_expression_dependencies sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
  WHERE (sed.referenced_schema_name = '<SCHEMA>' OR sed.referenced_schema_name IS NULL)
  AND  sed.referenced_entity_name = '<TYPE_NAME>'

UNION ALL 

  SELECT o.name, s.name AS sch, o.type, cRefobjects.level + 1 AS level
  FROM
    sys.sql_expression_dependencies AS sed
    INNER JOIN sys.objects o ON o.object_id = sed.referencing_id
    INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
    INNER JOIN cRefobjects ON sed.referenced_entity_name = cRefobjects.name
                           AND sed.referenced_schema_name = cRefobjects.sch
  )

    SELECT name, MAX(level) as level,  'EXEC sys.sp_refreshsqlmodule @name = ''' + sch + '.' + name + '''', type
    FROM cRefobjects
    GROUP BY name, sch, type
    ORDER BY level, type, name;

- Tomasz Maj

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- CodeCowboyOrg · Accepted Answer

描述

编写了以下存储过程，它递归地列出所有依赖的子对象和子对象的依赖项以及子对象的子对象等等。输入参数可以是存储过程、用户函数或视图。可以轻松修改以获取列5的唯一列表，而不管调用对象的级别有多深以及由哪个对象调用。

列

UsedByObjectId - 使用依赖对象的父对象
UsedByObjectName - 父对象的名称
UsedByObjectType - 父对象的类型（P、V、FN）
DependentObjectId - 父对象使用的子对象
DependentObjectName - 子对象的名称
DependentObjectType - 依赖子对象的类型（P、V、FN、U）
Level - 对象使用的嵌套递归级别有多深

代码

--=========================================================================
--=========================================================================
--== 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