August 01, 2012

View Dependencies SQL Server 2005

There is a built in feature in SQL Server 2005 to view the dependent objects, which is very easy to use. On SQL Server Management Studio, right-click the object, and then click 'View Dependencies' which will display a hierarchical view of objects that depend on the selected object. SQL server using the stored procedure 'sp_depends' internally for this. But unfortunately sp_depends does not give appropriate results always. In SQL Server 2008, we can use sys.dm_sql_referencing_entities to get this. But it is not there in SQL Sever 2005. So I am using the following query to know the usage of sql object across the whole database

from   syscomments a (nolock)
       inner join sysobjects b (nolock) on =

where  a.text like '%YOUR_TABLE%'