Export (0) Print
Expand All

View the Dependencies of a Stored Procedure

This topic describes how to view stored procedure dependencies in SQL Server 2014 by using SQL Server Management Studio or Transact-SQL.

Limitations and Restrictions

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.

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

Community Additions

ADD
Show:
© 2014 Microsoft