Search code examples
sql-server

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

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.

Solution

  • 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

    Source: SQL Authority