Identify the dependence of objects in MS SQL Server

Dependence means objects are used by another object like a table is used in the Stored procedures or view. So if we update that table one column then we need to update/verify its dependent object.

We can check in many alternative ways:

1. Use the SP_depend procedure:

EXEC sp_depends @objname = N'[Person].[Address]';

2. Use the SQL Server Management Studio to see the dependencies of the object:

3. Way is used, sometimes we are using stored procedures that using dynamic SQL execution for that it’s always good to check the routines table:

SELECT routine_name, routine_type FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%Address%'

OR

SELECT * FROM information_schema.routines ISR
 WHERE CHARINDEX('dbo.First', ISR.ROUTINE_DEFINITION) > 0

4. Using DMV sys.dm_sql_referencing_entities

-- Returning the entities that refer to a given entity:

SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc, is_caller_dependent
 FROM sys.dm_sql_referencing_entities ('dbo.First', 'OBJECT');

--Returning entities that are referenced by an object:

SELECT
        referenced_schema_name, referenced_entity_name, referenced_minor_name, 
        referenced_class_desc, is_caller_dependent, is_ambiguous
FROM sys.dm_sql_referenced_entities ('StoredProcedure', 'OBJECT');