报告 SQL 依赖关系

SQL 依赖关系是在 SQL 表达式中使用的按名称引用，可使一个用户定义的实体依赖于另一个实体。例如，视图和存储过程依赖于包含视图或过程返回的数据的表是否存在。报告依赖关系信息在下列情况下十分有用：

• 将模块（如存储过程）从一个应用程序移到另一个应用程序时。
  在移动模块之前，您可以确定是否存在必须随该模块一起移动的、由该模块引用的数据库或跨数据库实体。
  
• 在修改实体定义时，例如在表中添加或删除列时。
  在修改某实体之前，您可以确定是否有其他实体依赖于该实体的当前定义。在修改后查询或调用这些依赖实体时，可能会产生意外结果，并且可能会需要刷新元数据或修改它们的定义。
  
• 将一个或多个数据库从一台服务器移到另一台服务器时。
  将数据库移到另一台服务器之前，您可以确定一个数据库中的实体是否依赖于另一个数据库中的实体。这样，您就知道需将这些数据库移到同一服务器上。
  
• 为跨多个数据库的应用程序配置故障转移时。
  您希望确保应用程序随时可用，并使用数据库镜像作为故障转移策略。该应用程序依赖于多个数据库，而您希望确保在发生故障时由镜像服务器接替工作后，应用程序可以成功运行。因为镜像是在数据库级别工作，所以需要确定哪些数据库是应用程序的关键数据库，以便为它们分别设置镜像。然后，您可以确保将所有数据库一起实现故障转移，从而保证应用程序在镜像服务器上正常工作。
  
• 查找应用程序中使用由四部分组成的名称来执行分布式查询的实体时。
  您希望知道分布式查询中使用了哪些链接服务器。
  
• 查找应用程序中使用的实体是否包含依赖调用方的引用或名称由单个部分组成的引用时。
  部署应用程序之前，您可以确定应用程序使用的实体是否包含依赖调用方的引用或包含名称仅由一个部分组成的实体引用。此类引用表示不良的编程习惯，可能会导致部署应用程序时发生意外的行为。这是因为被引用实体的解析（绑定）依赖于调用方的架构，而且此信息直到运行时才能确定。找到这些引用之后，可以通过指定由多部分组成的适当名称来更正查询，例如 schema_name.object_name。
  

有关 SQL 依赖关系的详细信息，请参阅了解 SQL 依赖关系。

 使用系统视图和函数报告依赖关系

为了查看 SQL 依赖关系，SQL Server 2008 提供了 sys.sql_expression_dependencies 目录视图以及 sys.dm_sql_referenced_entities 和 sys.dm_sql_referencing_entities 动态管理函数。您可以查询这些对象以返回与用户定义的实体有关的依赖关系信息。

也可以通过使用 SQL Server Management Studio 中的“查看依赖关系”查看 SQL 依赖关系。有关详细信息，请参阅如何查看 SQL 依赖关系 (SQL Server Management Studio)。

使用 sys.sql_expression_dependencies 目录视图

sys.sql_expression_dependencies 目录视图为数据库所有者或数据库管理员提供了报告给定数据库的依赖关系信息的功能。使用此视图，可以回答以下全局问题：

• 数据库具有哪些跨服务器或跨数据库依赖关系？
  
• 数据库中存在哪些依赖关系？
  
• 数据库中的哪些实体具有依赖调用方的引用？
  
• 哪些服务器级或数据库级的 DDL 触发器与数据库中的实体存在依赖关系？
  
• 数据库中的哪些模块使用用户定义类型 (UDT)？
  

sys.sql_expression_dependencies 具有以下限制：

• 仅当指定了由四部分或三部分组成的有效名称时，才会返回跨服务器和跨数据库实体的依赖关系。但不会返回被引用实体的 ID。
  
• 只会报告针对绑定到架构的实体的列级依赖关系。
  

使用 sys.dm_sql_referenced_entities 动态管理函数

sys.dm_sql_referenced_entities 函数将对由指定引用实体定义中的名称所引用的每个用户定义实体返回一行。引用实体可以是用户定义对象、服务器级 DDL 触发器或数据库级 DDL 触发器。此信息与 sys.sql_expression_dependencies 返回的信息相同，但是，结果集仅包含由指定引用实体引用的那些实体。如果开发人员希望跟踪其拥有的或具有 VIEW DEFINITION 权限的模块的依赖关系，此函数非常有用。

使用 sys.dm_sql_referencing_entities 动态管理函数

sys.dm_sql_referencing_entities 函数将为当前数据库中按名称引用另一个用户定义实体的每个用户定义实体返回一行。引用实体可以是用户定义对象、类型（别名或 CLR UDT）、XML 架构集合或分区函数。如果开发人员希望跟踪他们所拥有的实体的依赖关系，此函数非常有用。例如，在修改用户定义类型之前，开发人员可以使用此函数确定数据库中所有依赖于此类型的实体。请注意，除非在计算列、CHECK 约束或 DEFAULT 约束的定义中指定了用户定义类型，否则将不会报告对表中用户定义类型的引用。

 示例

下面的示例通过使用 sys.sql_expression_dependencies 目录视图以及 sys.dm_sql_referenced_entities 和 sys.dm_sql_referencing_entities 动态管理函数返回 SQL 依赖关系。

报告指定实体所依赖的实体

您可以查询 sys.sql_expression_dependencies 目录视图或 sys.dm_sql_referenced_entities 动态管理函数以返回指定实体所依赖的实体的列表。例如，您可以返回由某个模块（如存储过程或触发器）引用的实体列表。

下例将创建一个表、一个视图和三个存储过程。这些对象将用在后面的查询中以演示如何报告依赖关系信息。可看到 MyView 和 MyProc3 均引用 Mytable。MyProc1 引用 MyView，而 MyProc2 引用 MyProc1。

下面的示例查询 sys.sql_expression_dependencies 目录视图以返回由 MyProc3 引用的实体。

下面是结果集：

referencing_entity server_name database_name  schema_name referenced_entity

------------------ ----------- -------------  ----------- -----------------

MyProc3            NULL        NULL           dbo         MyProc2

MyProc3            NULL        AdventureWorks dbo         MyTable

(2 row(s) affected)

此查询返回了两个在 MyProc3 定义中按名称引用的实体。服务器名称为 NULL，因为被引用实体没有使用有效的由四部分组成的名称指定。返回的结果中显示了 MyTable 的数据库名称，因为在存储过程中是使用由三部分组成的有效名称定义此实体的。

可以使用 sys.dm_sql_referenced_entities 返回类似信息。除了报告对象名称之外，此函数还可用于返回绑定到架构的实体以及非绑定到架构的实体的列级依赖关系。下面的示例将返回 MyProc3 所依赖并且包含列级依赖关系的实体。

下面是结果集：

server_name database_name     schema_name  referenced_entity  column_name

----------- ----------------- -----------  -----------------  -----------

NULL        NULL              dbo          MyProc2            NULL

NULL        AdventureWorks    dbo          MyTable            NULL

NULL        AdventureWorks    dbo          MyTable            c1

NULL        AdventureWorks    dbo          MyTable            c2

(4 row(s) affected)

在此结果集中，返回了两个相同的实体。此外，还返回了另外两行用于显示 MyTable 中的 c1 和 c2 列的依赖关系。请注意，在 MyProc3 的定义中，使用了一个 SELECT * 语句来引用 MyTable 中的列。不推荐使用此编码方式，不过，列级依赖关系仍将由数据库引擎跟踪。

至此，上述示例已经阐释了如何返回某个实体所直接依赖的实体。下面的示例将使用递归公用表表达式 (CTE) 返回实体的所有直接和间接依赖关系。

下面是结果集：

entity_name  referenced_schema  referenced_entity  level

-----------  -----------------  -----------------  -----

MyProc3      dbo                MyProc2            0

MyProc3      dbo                MyTable            0

MyProc2      dbo                MyProc1            1

MyProc1      dbo                MyView             2

MyView       dbo                MyTable            3

(5 row(s) affected)

在此结果集中，MyProc2 和 MyTable 的级别值均为 0，表明它们都作为直接依赖项返回。第三行显示对 MyProc1 的间接依赖关系，在 MyProc2 的定义中引用了该项。第四行显示对 MyView 的依赖关系，在 MyProc1 的定义中引用了该项；最后一行是对 MyTable 的依赖关系，在 MyView 的定义中引用了该项。

通过返回分层依赖关系信息，您可以确定给定实体的直接和间接依赖关系的完整列表，并以此推断如果需要将这些对象移至另一个数据库时应遵循的部署顺序。

下面的示例将使用 sys.dm_sql_referenced_entities 函数返回相同的分层依赖关系信息。系统将返回 MyProc3 所依赖并且包含列级依赖关系的实体。

报告依赖于指定实体的实体

您可以查询 sys.sql_expression_dependencies 目录视图或 sys.dm_sql_referencing_entities 动态管理函数以返回依赖于指定实体的实体列表。例如，如果指定实体是一个表，则在定义中按名称引用该表的所有实体都将返回。

下面的示例将返回引用实体 dbo.MyTable 的实体。

可以使用 sys.dm_sql_referenced_entities 动态管理函数返回类似信息。

报告列级依赖关系

可以通过使用 sys.dm_sql_referenced_entities 报告绑定到架构的实体以及非绑定到架构的实体的列级依赖关系。此外，您还可以使用 sys.sql_expression_dependencies 报告绑定到架构的实体的列级依赖关系。

下面的示例通过查询 sys.dm_sql_referenced_entities 报告非绑定到架构的实体的列级依赖关系。该示例首先创建 Table1 和 Table 2 以及存储过程 Proc1。该过程引用 Table1 中的 b 列和 c 列以及 Table2 中的 c2 列。视图 sys.dm_sql_referenced_entities 和指定为引用实体的存储过程一起运行。结果集包含被引用实体 Table1 和 Table2 的行以及在存储过程的定义中引用的列。请注意，在引用这些表的行中的 column_name 列中返回了 NULL。

下面是结果集：

referenced_id, table_name,  column_name

-------------  -----------  -------------

151671588      Table1       NULL

151671588      Table1       b

151671588      Table1       c

2707154552     Table2       NULL

2707154552     Table2       c2

报告跨服务器和跨数据库的依赖关系

当某个实体通过使用由三部分组成的有效名称引用另一个实体时，将创建跨数据库的依赖关系。当某个实体通过使用由四部分组成的有效名称引用另一个实体时，将创建跨服务器的引用。仅当显式指定服务器和数据库的名称时，才会记录相应的名称。例如，当指定为 MyServer.MyDB.MySchema.MyTable 时，会记录服务器和数据库的名称，但是，当指定为 MyServer..MySchema.MyTable 时，将只记录服务器的名称。有关如何对跨服务器和跨数据库的依赖关系进行跟踪的详细信息，请参阅了解 SQL 依赖关系。

可以通过使用 sys.sql_expression_dependencies 或 sys.dm_sql_referenced_entitites 报告跨数据库和跨服务器的依赖关系。

下面的示例将返回所有跨数据库的依赖关系。此示例首先创建数据库 db1 以及两个引用数据库 db2 和 db3 中的表的存储过程。然后，对 sys.sql_expression_dependencies 表进行查询，以报告这两个过程和表之间的跨数据库依赖关系。请注意，在被引用实体 t3 的 referenced_schema_name 列中返回 NULL，原因是过程定义中没有为该实体指定架构名称。

报告依赖调用方的引用

依赖调用方的引用表示被引用实体的架构绑定在运行时发生，因此，实体 ID 的解析依赖于调用方的默认架构。通常，此类绑定称为“动态架构绑定”，如果被引用实体是在 EXECUTE 语句中并非通过指定架构名称来调用的存储过程、扩展存储过程或者非绑定到架构的用户定义函数，将会发生此类绑定。例如，采用格式 EXECUTE MySchema.MyProc 对实体的引用并不是依赖调用方的引用，但采用格式 EXECUTE MyProc 的引用是依赖调用方的引用。

当执行依赖调用方的引用所在的模块时，此类引用可能会导致意外行为。例如，请看下面这个存储过程，它使用一个由单个部分组成的名称引用其他过程。

执行 Proc1 后，Proc2 会绑定到调用方的架构。假定 Proc1 由具有默认架构 S1 的 User1 执行，并由具有默认架构 S2 的 User2 执行。由 User1 执行 Proc1 时，被引用实体解析为 S1.Proc2。由 User2 执行 Proc1 时，被引用实体解析为 S2.Proc2。受此行为影响，执行 Proc1 后才能解析 Proc2 的 ID，因此，sys.sql_expression_dependencies 视图和 sys.dm_sql_referenced_entities 函数中的 is_caller_dependent 列均设为 1。执行 Proc1 后，数据库引擎将在调用方的默认架构中查找被引用实体 Proc2。如果未找到，将检查 dbo 架构。如果在 dbo 架构中找不到 Proc2，则将无法解析 Proc2 的 ID，并且该语句失败。建议在引用数据库实体时指定由两部分组成的名称，以便消除可能的应用程序错误。

下面的示例将返回当前数据库中包含依赖调用方引用的每个实体。

报告使用指定 UDT 的实体

下面的示例返回当前数据库中在定义中引用了指定类型的每个实体。结果集中显示了使用此类型的两个存储过程。在 HumanResources.Employee 表的若干列的定义中也使用了此类型，但是，因为此类型没有包含在表中的计算列、CHECK 约束或 DEFAULT 约束的定义中，所以不会返回该表的任何行。

报告服务器级 DDL 触发器依赖关系

仅当上下文设置为 master 数据库时，才可以使用 sys.sql_expression_dependencies 和 sys.dm_sql_referencing_entities 报告服务器级 DDL 触发器的依赖关系。使用 sys.dm_sql_referenced_entities 函数时，上下文可以是任意数据库。

下面的示例查询 sys.sql_expression_dependencies 视图以报告服务器级 DDL 触发器的依赖关系。

原文地址：http://msdn.microsoft.com/zh-cn/partners/bb677168.aspx

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-618268/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16436858/viewspace-618268/