Skip to content

Latest commit

 

History

History
272 lines (214 loc) · 13.1 KB

view-the-dependencies-of-a-stored-procedure.md

File metadata and controls

272 lines (214 loc) · 13.1 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords monikerRange
View the Dependencies of a Stored Procedure
Learn how to view stored procedure dependencies in the SQL Database Engine by using SQL Server Management Studio or Transact-SQL.
WilliamDAssafMSFT
wiassaf
03/14/2017
sql
stored-procedures
how-to
ignite-2024
stored procedures [SQL Server], dependencies
displaying stored procedure dependencies
viewing stored procedure dependencies
>=aps-pdw-2016 || =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

View the Dependencies of a Stored Procedure

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics PDW FabricSQLDB]

This topic describes how to view stored procedure dependencies in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Before You Begin

Limitations and Restrictions

Security

Permissions

System Function: sys.dm_sql_referencing_entities
Requires CONTROL permission on the referenced entity and SELECT permission on sys.dm_sql_referencing_entities. When the referenced entity is a partition function, CONTROL permission on the database is required. By default, SELECT permission is granted to public.

System Function: sys.dm_sql_referenced_entities
Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW DEFINITION permission on the referencing entity. By default, SELECT permission is granted to public. 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. Requires VIEW ANY DEFINITION permission on the server when the referencing entity is a server-level DDL trigger.

Object Catalog View: sys.sql_expression_dependencies
Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. By default, SELECT permission is granted only to members of the db_owner fixed database role. 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:

Using SQL Server Management Studio

To view the dependencies of a procedure in Object Explorer

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE] 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.

Using Transact-SQL

[!INCLUDE article-uses-adventureworks]

To view the dependencies of a procedure in Query Editor

System Function: sys.dm_sql_referencing_entities
This function is used to display the objects that depend on a procedure.

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE] 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. The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the [!INCLUDEssSampleDBCoFull] database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2022;  
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. 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 AdventureWorks2022;  
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

System Function: sys.dm_sql_referenced_entities
This function is used to display the objects a procedure depends on.

  1. In Object Explorer, connect to an instance of [!INCLUDEssDE] 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. The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the [!INCLUDEssSampleDBCoFull] database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2022;  
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. 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 AdventureWorks2022;  
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

Object 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. In Object Explorer, connect to an instance of [!INCLUDEssDE] 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. The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the [!INCLUDEssSampleDBCoFull] database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2022;  
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. 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 AdventureWorks2022;  
GO  
SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,  
    OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_description,   
    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. In Object Explorer, connect to an instance of [!INCLUDEssDE] 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. The first example creates the uspVendorAllInfo procedure, which returns the names of all the vendors in the [!INCLUDEssSampleDBCoFull] database, the products they supply, their credit ratings, and their availability.

    USE AdventureWorks2022;  
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. After the procedure is created, the second example uses the sys.sql_expression_dependencies view to display the objects the procedure depends on.

    USE AdventureWorks2022;  
GO  
SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_description,   
    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_referenced_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)