查看存储过程的依赖关系View the Dependencies of a Stored Procedure

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

本主题介绍了如何使用 SQL Server 2017SQL Server 2017SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQL中查看存储过程依赖关系。This topic describes how to view stored procedure dependencies in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL.

开始之前Before You Begin

限制和局限Limitations and Restrictions

SecuritySecurity

权限Permissions

系统函数: sys.dm_sql_referencing_entitiesSystem Function: sys.dm_sql_referencing_entities
要求对被引用的实体拥有 CONTROL 权限,并且对 sys.dm_sql_referencing_entities 拥有 SELECT 权限。Requires CONTROL permission on the referenced entity and SELECT permission on sys.dm_sql_referencing_entities. 当被引用的实体是分区函数时,要求对数据库拥有 CONTROL 权限。When the referenced entity is a partition function, CONTROL permission on the database is required. 默认情况下,SELECT 权限授予 public。By default, SELECT permission is granted to public.

系统函数: sys.dm_sql_referenced_entitiesSystem Function: sys.dm_sql_referenced_entities
要求对 sys.dm_sql_referenced_entities 拥有 SELECT 权限并对引用实体拥有 VIEW DEFINITION 权限。Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW DEFINITION permission on the referencing entity. 默认情况下,SELECT 权限授予 public。By default, SELECT permission is granted to public. 要求对数据库拥有 VIEW DEFINITION 权限或 ALTER DATABASE DDL TRIGGER 权限(当引用实体为数据库级 DDL 触发器时)。Requires VIEW DEFINITION permission on the database or ALTER DATABASE DDL TRIGGER permission on the database when the referencing entity is a database-level DDL trigger. 当引用实体为服务器级 DDL 触发器时,要求对服务器拥有 VIEW ANY DEFINITION 权限。Requires VIEW ANY DEFINITION permission on the server when the referencing entity is a server-level DDL trigger.

对象目录视图: sys.sql_expression_dependenciesObject Catalog View: sys.sql_expression_dependencies
要求对数据库具有 VIEW DEFINITION 权限,并对数据库的 sys.sql_expression_dependencies 具有 SELECT 权限。Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. 默认情况下,SELECT 权限仅授予 db_owner 固定数据库角色的成员。By default, SELECT permission is granted only to members of the db_owner fixed database role. 将 SELECT 和 VIEW DEFINITION 权限授予其他用户时,被授权者可以查看数据库中的所有依赖关系。When SELECT and VIEW DEFINITION permissions are granted to another user, the grantee can view all dependencies in the database.

如何查看存储过程的依赖关系How to View the Dependencies of a Stored Procedure

您可以使用以下项之一:You can use one of the following:

使用 SQL Server Management StudioUsing SQL Server Management Studio

在对象资源管理器中查看过程的依赖关系To view the dependencies of a procedure in Object Explorer

  1. 在对象资源管理器中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” 、过程所属的数据库以及 “可编程性”Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.

  3. 展开 “存储过程”,右键单击此过程,再单击 “查看依赖关系”。Expand Stored Procedures, right-click the procedure and then click View Dependencies.

  4. 查看依赖于过程的对象的列表。View the list of objects that depend on the procedure.

  5. 查看过程所依赖的对象的列表。View the list of objects on which the procedure depends.

  6. 单击“确定” 。Click OK.

使用 Transact-SQLUsing Transact-SQL

在查询编辑器中查看过程的依赖关系To view the dependencies of a procedure in Query Editor

系统函数: sys.dm_sql_referencing_entitiesSystem Function: sys.dm_sql_referencing_entities
此函数用于显示依赖于过程的对象。This function is used to display the objects that depend on a procedure.

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” ,然后展开过程所属的数据库。Expand Databases, expand the database in which the procedure belongs.

  3. “文件” 菜单下,单击 “新建查询”Click on New Query under the File menu.

  4. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following examples into the query editor. 第一个示例创建 uspVendorAllInfo 过程,该过程返回 Adventure Works CyclesAdventure Works Cycles 数据库中所有供应商的名称、所提供的产品、信用等级以及可用性。The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the Adventure Works CyclesAdventure Works Cycles database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2008R2;  
    GO  
    IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL   
        DROP PROCEDURE Purchasing.uspVendorAllInfo;  
    GO  
    CREATE PROCEDURE Purchasing.uspVendorAllInfo  
    WITH EXECUTE AS CALLER  
    AS  
        SET NOCOUNT ON;  
        SELECT v.Name AS Vendor, p.Name AS 'Product name',   
          v.CreditRating AS 'Rating',   
          v.ActiveFlag AS Availability  
        FROM Purchasing.Vendor v   
        INNER JOIN Purchasing.ProductVendor pv  
          ON v.BusinessEntityID = pv.BusinessEntityID   
        INNER JOIN Production.Product p  
          ON pv.ProductID = p.ProductID   
        ORDER BY v.Name ASC;  
    GO  
    
  5. 创建该过程后,第二个示例使用 sys.dm_sql_referencing_entities 函数来显示依赖于该过程的对象。After the procedure is created, the second example uses the sys.dm_sql_referencing_entities function to display the objects that depend on the procedure.

    USE AdventureWorks2012;  
    GO  
    SELECT referencing_schema_name, referencing_entity_name, referencing_id, referencing_class_desc, is_caller_dependent  
    FROM sys.dm_sql_referencing_entities ('Purchasing.uspVendorAllInfo', 'OBJECT');   
    GO  
    
    

系统函数: sys.dm_sql_referenced_entitiesSystem Function: sys.dm_sql_referenced_entities
此函数用于显示过程所依赖的对象。This function is used to display the objects a procedure depends on.

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” ,然后展开过程所属的数据库。Expand Databases, expand the database in which the procedure belongs.

  3. “文件” 菜单下,单击 “新建查询”Click on New Query under the File menu.

  4. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following examples into the query editor. 第一个示例创建 uspVendorAllInfo 过程,该过程返回 Adventure Works CyclesAdventure Works Cycles 数据库中所有供应商的名称、所提供的产品、信用等级以及可用性。The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the Adventure Works CyclesAdventure Works Cycles database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2008R2;  
    GO  
    IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL   
        DROP PROCEDURE Purchasing.uspVendorAllInfo;  
    GO  
    CREATE PROCEDURE Purchasing.uspVendorAllInfo  
    WITH EXECUTE AS CALLER  
    AS  
        SET NOCOUNT ON;  
        SELECT v.Name AS Vendor, p.Name AS 'Product name',   
          v.CreditRating AS 'Rating',   
          v.ActiveFlag AS Availability  
        FROM Purchasing.Vendor v   
        INNER JOIN Purchasing.ProductVendor pv  
          ON v.BusinessEntityID = pv.BusinessEntityID   
        INNER JOIN Production.Product p  
          ON pv.ProductID = p.ProductID   
        ORDER BY v.Name ASC;  
    GO  
    
  5. 创建该过程后,第二个示例使用 sys.dm_sql_referenced_entities 函数来显示该过程依赖的对象。After the procedure is created, the second example uses the sys.dm_sql_referenced_entities function to display the objects that the procedure depends on.

    USE AdventureWorks2012;  
    GO  
    SELECT referenced_schema_name, referenced_entity_name,  
    referenced_minor_name,referenced_minor_id, referenced_class_desc,  
    is_caller_dependent, is_ambiguous  
    FROM sys.dm_sql_referenced_entities ('Purchasing.uspVendorAllInfo', 'OBJECT');  
    GO  
    

对象目录视图: sys.sql_expression_dependenciesObject Catalog View: sys.sql_expression_dependencies
此视图可以用于显示过程所依赖的对象或依赖于过程的对象。This view can be used to display objects that a procedure depends on or that depend on a procedure.

显示依赖于过程的对象。Displaying the objects that depend on a procedure.

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” ,然后展开过程所属的数据库。Expand Databases, expand the database in which the procedure belongs.

  3. “文件” 菜单下,单击 “新建查询”Click on New Query under the File menu.

  4. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following examples into the query editor. 第一个示例创建 uspVendorAllInfo 过程,该过程返回 Adventure Works CyclesAdventure Works Cycles 数据库中所有供应商的名称、所提供的产品、信用等级以及可用性。The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the Adventure Works CyclesAdventure Works Cycles database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2008R2;  
    GO  
    IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL   
        DROP PROCEDURE Purchasing.uspVendorAllInfo;  
    GO  
    CREATE PROCEDURE Purchasing.uspVendorAllInfo  
    WITH EXECUTE AS CALLER  
    AS  
        SET NOCOUNT ON;  
        SELECT v.Name AS Vendor, p.Name AS 'Product name',   
          v.CreditRating AS 'Rating',   
          v.ActiveFlag AS Availability  
        FROM Purchasing.Vendor v   
        INNER JOIN Purchasing.ProductVendor pv  
          ON v.BusinessEntityID = pv.BusinessEntityID   
        INNER JOIN Production.Product p  
          ON pv.ProductID = p.ProductID   
        ORDER BY v.Name ASC;  
    GO  
    
  5. 创建该过程后,第二个示例使用 sys.sql_expression_dependencies 视图来显示依赖于该过程的对象。After the procedure is created, the second example uses the sys.sql_expression_dependencies view to display the objects that depend on the procedure.

    USE AdventureWorks2012;  
    GO  
    SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,  
        OBJECT_NAME(referencing_id) AS referencing_entity_name,   
        o.type_desc AS referencing_desciption,   
        COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
        referencing_class_desc, referenced_class_desc,  
        referenced_server_name, referenced_database_name, referenced_schema_name,  
        referenced_entity_name,   
        COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
        is_caller_dependent, is_ambiguous  
    FROM sys.sql_expression_dependencies AS sed  
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
    WHERE referenced_id = OBJECT_ID(N'Purchasing.uspVendorAllInfo')  
    GO  
    

显示过程所依赖的对象。Displaying the objects a procedure depends on.

  1. “对象资源管理器” 中,连接到 数据库引擎Database Engine 的实例,然后展开该实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine and then expand that instance.

  2. 展开 “数据库” ,然后展开过程所属的数据库。Expand Databases, expand the database in which the procedure belongs.

  3. “文件” 菜单下,单击 “新建查询”Click on New Query under the File menu.

  4. 复制以下示例并将其粘贴到查询编辑器中。Copy and paste the following examples into the query editor. 第一个示例创建 uspVendorAllInfo 过程,该过程返回 Adventure Works CyclesAdventure Works Cycles 数据库中所有供应商的名称、所提供的产品、信用等级以及可用性。The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the Adventure Works CyclesAdventure Works Cycles database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2008R2;  
    GO  
    IF OBJECT_ID ( 'Purchasing.uspVendorAllInfo', 'P' ) IS NOT NULL   
        DROP PROCEDURE Purchasing.uspVendorAllInfo;  
    GO  
    CREATE PROCEDURE Purchasing.uspVendorAllInfo  
    WITH EXECUTE AS CALLER  
    AS  
        SET NOCOUNT ON;  
        SELECT v.Name AS Vendor, p.Name AS 'Product name',   
          v.CreditRating AS 'Rating',   
          v.ActiveFlag AS Availability  
        FROM Purchasing.Vendor v   
        INNER JOIN Purchasing.ProductVendor pv  
          ON v.BusinessEntityID = pv.BusinessEntityID   
        INNER JOIN Production.Product p  
          ON pv.ProductID = p.ProductID   
        ORDER BY v.Name ASC;  
    GO  
    
  5. 创建该过程后,第二个示例使用 sys.sql_expression_dependencies 视图来显示该过程依赖的对象。After the procedure is created, the second example uses the sys.sql_expression_dependencies view to display the objects the procedure depends on.

    USE AdventureWorks2012;  
    GO  
    SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,   
        o.type_desc AS referencing_desciption,   
        COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
        referencing_class_desc, referenced_class_desc,  
        referenced_server_name, referenced_database_name, referenced_schema_name,  
        referenced_entity_name,   
        COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
        is_caller_dependent, is_ambiguous  
    FROM sys.sql_expression_dependencies AS sed  
    INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
    WHERE referencing_id = OBJECT_ID(N'Purchasing.uspVendorAllInfo');  
    GO  
    

另请参阅See Also

重命名存储过程 Rename a Stored Procedure
sys.dm_sql_referencing_entities (Transact-SQL) sys.dm_sql_referencing_entities (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL) sys.dm_sql_referenced_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)sys.sql_expression_dependencies (Transact-SQL)