How to find all the dependencies of a table in sql server

Question

I have a database where i have list of tables,procedures,views and triggers. But i want a query to get all the dependencies of a table including child tables which are referring the parent table.

Answer 1

The following are the ways we can use to check the dependencies:

Method 1: Using sp_depends

 sp_depends 'dbo.First'  GO 

Method 2: Using information_schema.routines

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

Method 3: Using DMV sys.dm_sql_referencing_entities

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

Answer 2

In SQL Server 2008 there are two new Dynamic Management Functions introduced to keep track of object dependencies: sys.dm_sql_referenced_entities and sys.dm_sql_referencing_entities:

1/ Returning the entities that refer to a given entity:

SELECT         referencing_schema_name, referencing_entity_name,          referencing_class_desc, is_caller_dependent FROM sys.dm_sql_referencing_entities ('<TableName>', 'OBJECT') 

2/ 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 ('<StoredProcedureName>', 'OBJECT'); 

Alternatively, you can use sp_depends:

EXEC sp_depends '<TableName>' 

Another option is to use a pretty useful tool called SQL Dependency Tracker from Red Gate.