Find Referenced or Referencing Object in SQL Server

Introduction

We are creating the Table Objects in our database and this Table objects is referred in different type of referencing objects.

For an example if we have a table named tbl_Employee and it is used in the Procedure named proc_Employee and Function named func_Employee. So here the Table object is called Reference Object and the Procedure and Function is called Referencing Object.

Without any understanding about the Referencing Object (Procedure, Function etc), if we Delete or Modified the Reference Object it would be dangerous for us.

This article is discussed about it.

How to find the Referencing Object

The catalog view named sys.sql_expression_dependencies is used to find the referencing objects.

Step-1 [ Create the Reference Objects ]

IF OBJECT_ID(N'dbo.tbl_Employee', N'U') IS NOT NULL

   BEGIN

     DROP TABLE dbo.tbl_Employee;

   END

GO

CREATE TABLE dbo.tbl_Employee

       (

         EMPID    INT         NOT NULL IDENTITY PRIMARY KEY,

         EMPNAME  VARCHAR(50) NOT NULL

       );

GO 

Step-2 [ Create the Referencing Objects ]

-- Procedure which refer the Table Object [tbl_Employee]   

IF OBJECT_ID(N'dbo.proc_Employee', N'P') IS NOT NULL

  BEGIN

    DROP PROCEDURE dbo.proc_Employee;

  END 

GO

CREATE PROCEDURE dbo.proc_Employee

AS

BEGIN

   SELECT * FROM dbo.tbl_Employee;

END

GO

-- Function  which refer the Table Object [tbl_Employee]

IF OBJECT_ID(N'dbo.func_Employee', N'FV') IS NOT NULL

  BEGIN

    DROP FUNCTION dbo.func_Employee;

  END 

GO

CREATE FUNCTION dbo.func_Employee

    (

      @p_EMPID   INT

    )

RETURNS VARCHAR(50)

AS

BEGIN

   DECLARE @v_RETURN VARCHAR(50);

  

   SET @v_RETURN=(SELECT EMPNAME

                  FROM   tbl_Employee

                  WHERE  EMPID = @p_EMPID);

                 

   RETURN @v_RETURN;

END                 

GO

Step-3 [ Find Relation ]

SELECT o.name AS [Referencing Object Name],

       o.type_desc AS [Referencing Object Type],

       referenced_entity_name AS [Reference Object Name],

       so1.type_desc AS [Reference Object Type]

FROM   sys.sql_expression_dependencies sed

       INNER JOIN sys.objects o ON sed.referencing_id = o.object_id

       LEFT OUTER JOIN sys.objects so1 ON sed.referenced_id =so1.object_id

WHERE  referenced_entity_name = 'tbl_Employee'

Step-4 [ Observe the Output ]

Referencing Object Name    Referencing Object Type        Reference Object Name     Reference Object Type

func_Employee                    SQL_SCALAR_FUNCTION       tbl_Employee                      USER_TABLE

proc_Employee                    SQL_STORED_PROCEDURE    tbl_Employee                      USER_TABLE

Hope you like it.

Posted by: MR. JOYDEEP DAS