Get all procs that are called from another procedure

if exists (select name from sysobjects 
where name = '_GetInsideStoredProcs' and type = 'P')
drop procedure _GetinsideStoredProcs
GO
create proc _GetInsideStoredProcs
@Procname varchar(50)
as
/*
This script shows how to get names of all stored procedures called from inside of specified stored procedure

usage:
 exec _GetInsideStoredProcs 'YourProcName'

Created by Eva Zadoyen
 05/02/2002
*/set nocount on

create table #holdvalues
(_id int IDENTITY,
 _name varchar(100))

declare @sqlstr nvarchar(4000),
@obj_id int,
@text varchar(8000),
@ParmDefinition NVARCHAR(500)

insert  #holdvalues (_name)  select name  from sysobjects where type = 'P'
--select * from #holdvalues
set @sqlstr = N'select @id_out = (select object_id(@p_name))'
SET @ParmDefinition = N'@p_name varchar(50),
@id_OUT int OUTPUT'
EXEC sp_executesql
@SQLStr,
@ParmDefinition,
@p_name = @procname,
@id_out=@obj_id OUTPUT

create table #holdtext
(_id int IDENTITY,
 _text nvarchar(4000))


SET @sqlstr =N'insert #holdtext (_text) SELECT text FROM syscomments WHERE ID =@obj_idIN'
SET @ParmDefinition = N'@obj_idIN int'

EXEC sp_executesql
@SQLStr,
@ParmDefinition,
@obj_idIN = @obj_id

SET @sqlstr = N'select distinct @p_name "Proc Name", v._name "Called Proc" 
from #holdvalues v right outer join #holdtext t on t._text like ''%'' +  v._name + ''%'' 
where v._name is not null and v._name <> @p_name'
SET @ParmDefinition = N'@p_name varchar(50)'

EXEC sp_executesql
@SQLStr,
@ParmDefinition,
@p_name = @procname

set nocount off
return