View the Dependencies of a Stored Procedure

  • Article

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