Get depended object from SP_Depends and Information_Schema.routines in SQL Server 2008

SP_Depends is very useful stored procedure which can give you list of the dependent object of your table or views. If you pass table name in SP_Depends, it will give you depended views, stored procedures, functions etc. let us look at this by small demo.

--create one small database for testing

create database DependCheck

go

use DependCheck;

go

--create one table and insert some data

create table TestDepend

(

ID Int Identity(1,1),

Name Varchar(20)

)

GO

Insert into TestDepend

SELECT 'RITESH' UNION ALL

SELECT 'RAJAN' UNION ALL

SELECT 'ALKA'

go

--create one PROC which is depend on TestDepend Table

CREATE PROC uspTestDependSelectAll

AS

SELECT * FROM TestDepend

GO

--try executing PROC

EXEC uspTestDependSelectAll

GO

--check SP_Depends system procedure to get all dependent objects

sp_depends 'TestDepend'

GO

--here is one alternate way to get dependent list

SELECT * FROM information_schema.routines ISR WHERE charindex('TestDepend', ISR.ROUTINE_DEFINITION)>0

GO

--USE master;

--GO

--DROP DATABASE DEPENDCHECK

--GO

Well we have seen two different ways to get list of dependent objects of tables but in my live database when I run both way, it gives me different results however in our case it will give same results. So the question I would like to ask is, which way is correct and reliable?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com