View the Dependencies of a Stored Procedure
Applies to:
SQL Server
Azure SQL Database
Azure Synapse Analytics
Analytics Platform System (PDW)
This topic describes how to view stored procedure dependencies in SQL Server by using SQL Server Management Studio or Transact-SQL.
Before you begin: Limitations and Restrictions, Security
To view the dependencies of a procedure, using: SQL Server Management Studio, Transact-SQL
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
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs, and then expand Programmability.
Expand Stored Procedures, right-click the procedure and then click View Dependencies.
View the list of objects that depend on the procedure.
View the list of objects on which the procedure depends.
Click OK.
Using Transact-SQL
The Transact-SQL code samples in this article use the AdventureWorks2022
or AdventureWorksDW2022
sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.
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.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs.
Click on New Query under the File menu.
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 Adventure Works Cycles 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
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.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs.
Click on New Query under the File menu.
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 Adventure Works Cycles 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
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.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs.
Click on New Query under the File menu.
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 Adventure Works Cycles 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
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_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.
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the database in which the procedure belongs.
Click on New Query under the File menu.
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 Adventure Works Cycles 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
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_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_referenced_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)