View the Dependencies of a Stored Procedure

This topic describes how to view stored procedure dependencies in SQL Server 2012 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, PowerShell

Before You Begin

Limitations and Restrictions

[Top]

Security

Permissions

[Top]

• 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:

• SQL Server Management Studio

• Transact-SQL

Using SQL Server Management Studio

To view the dependencies of a procedure in Object Explorer

1. 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.

[Top]

Using Transact-SQL

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 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. 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 AdventureWorks2012;
     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 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
     

• 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 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. 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 AdventureWorks2012;
     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 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_referencing_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 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. 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 AdventureWorks2012;
       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 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. 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. 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 AdventureWorks2012;
       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 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

Reference

sys.dm_sql_referencing_entities (Transact-SQL)

sys.dm_sql_referenced_entities (Transact-SQL)

sys.sql_expression_dependencies (Transact-SQL)

Concepts

Rename a Stored Procedure