Get stored procs that call a specified procedure

if exists (select name from sysobjects 
where name = '_GetStoredProcs' and type = 'P')
drop procedure _GetStoredProcs
GO
create proc _GetStoredProcs

@Procname varchar(20)
as
/*
This script will list all stored procedures that are calling the specified stored procedure.
Also could be used to find procedures that contain any specified expression or words.

Usage:
exec _GetStoredProcs "test_proc"

Created by E.Zadoyen
05/01/2002
*/
declare @sqlstr varchar(200)

select @sqlstr = 'SELECT DISTINCT"' + @Procname +'" "TEST", object_name(id) "Exists In Proc"
 FROM syscomments WHERE  charindex (" '+ ltrim(@procname) + '",text )<>0
AND object_name(id) <> "' + LTRIM(RTRIM(@procname)) + '"'
exec(@sqlstr)

return