how to identify stored procedure dependencies?

Hello,
I need to find where an sp is called from another sp or how many sp's a main
sp is calling. Here is something that I tried with no luck. I know that
this sp is calling other sp's or is being called by other sp's. How can I
get a list of sp's related to this'
--this does not return anything for me even though I know there are
dependencies
select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
OBJECT_NAME([depid]) in (select specific_name from
information_schema.routines where routine_type = 'sp_Compare_Add')
order by Proce
Thanks,
Rich1. See what the undocumented procedure master..sp_MSdependencies tells
you. However, this may sometimes miss references.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
3. Run a SQL Profiler event trace.
http://msdn.microsoft.com/library/d...
ethowto15.asp
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
> Hello,
> I need to find where an sp is called from another sp or how many sp's a
> main
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Here is a query which will give you the listings for procedure to procedure
dependencies:
select object_name([id]) as [Procedure],
object_name([depid]) as [Depends on Procedure]
from dbo.[sysdepends]
where objectproperty([id], 'IsProcedure') = 1
and objectproperty([depid], 'IsProcedure') = 1
and objectproperty([id], 'IsMSShipped') = 0
Order by [id], [depid]
Note that there is the possibility due to late binding data could be missing
here
HTH-
--Tony
"Rich" wrote:

> Hello,
> I need to find where an sp is called from another sp or how many sp's a ma
in
> sp is calling. Here is something that I tried with no luck. I know that
> this sp is calling other sp's or is being called by other sp's. How can I
> get a list of sp's related to this'
> --this does not return anything for me even though I know there are
> dependencies
> select DISTINCT OBJECT_NAME([id]) Proce FROM sysdepends WHERE
> OBJECT_NAME([depid]) in (select specific_name from
> information_schema.routines where routine_type = 'sp_Compare_Add')
> order by Proce
>
> Thanks,
> Rich|||Thanks very much.
2. You can query the syscomments table like so:
select object_name(id) as obj_name from syscomments where text like
'%usp_mysp%'
This comment did the trick. I located what I needed.
"JT" wrote:

> 1. See what the undocumented procedure master..sp_MSdependencies tells
> you. However, this may sometimes miss references.
> 2. You can query the syscomments table like so:
> select object_name(id) as obj_name from syscomments where text like
> '%usp_mysp%'
> 3. Run a SQL Profiler event trace.
> http://msdn.microsoft.com/library/d...enethowto15.asp
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:D0ADF637-E524-4A83-96C0-7E343C13899F@.microsoft.com...
>
>|||Hi , you can also do this
CREATE PROC sp_search_code
(
@.SearchStr varchar(100),
@.RowsReturned int = NULL OUT
)
AS
BEGIN
SET NOCOUNT ON
SELECT DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object
name',
CASE
WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
THEN 'Replication stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
THEN 'Extended stored procedure'
WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
THEN 'Stored Procedure'
WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
THEN 'Trigger'
WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
THEN 'Table-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
THEN 'Scalar-valued function'
WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
THEN 'Inline function'
END AS 'Object type',
'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) +
'''' AS 'Run this command to see the object text'
FROM syscomments c
INNER JOIN
sysobjects o
ON c.id = o.id
WHERE c.text LIKE '%' + @.SearchStr + '%' AND
encrypted = 0 AND
(
OBJECTPROPERTY(c.id, 'IsReplProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1 OR
OBJECTPROPERTY(c.id, 'IsProcedure') = 1 OR
OBJECTPROPERTY(c.id, 'IsTrigger') = 1 OR
OBJECTPROPERTY(c.id, 'IsTableFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1 OR
OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
)
ORDER BY 'Object type', 'Object name'
SET @.RowsReturned = @.@.ROWCOUNT
END