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
DROP TABLE dbo.tbl_Employee;
CREATE TABLE dbo.tbl_Employee
EMPID INT NOT NULL IDENTITY PRIMARY KEY,
EMPNAME VARCHAR(50) NOT NULL
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
DROP PROCEDURE dbo.proc_Employee;
CREATE PROCEDURE dbo.proc_Employee
SELECT * FROM dbo.tbl_Employee;
-- Function which refer the Table Object [tbl_Employee]
IF OBJECT_ID(N'dbo.func_Employee', N'FV') IS NOT NULL
DROP FUNCTION dbo.func_Employee;
CREATE FUNCTION dbo.func_Employee
DECLARE @v_RETURN VARCHAR(50);
SET @v_RETURN=(SELECT EMPNAME
WHERE EMPID = @p_EMPID);
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