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

select b.name,b.xtype,a.text
from   syscomments a (nolock)
       inner join sysobjects b (nolock) on a.id = b.id

where  a.text like '%YOUR_TABLE%'

1 comment:

  1. ഒന്നും മനസ്സിലായില്ല...

    ReplyDelete