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