RE: What stored procedures call this stored procedure?

From: Joe Palm (JoePalm_at_discussions.microsoft.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 08:21:04 -0700

Alejandro,

I ran all of these examples, and it still isn't giving me what I'm after.
In the system tables, I can easily get what tables are used by which stored
procedures. Those relationships are maintained in the system tables and
easily discernible.

However, I see no such maintained relationships in the system tables between
a stored procedure and other stored procedures. But perhaps I'm looking for
this in the wrong way - I have the ID of a stored procedure and I'm looking
for that same ID in system tables other than "sysobjects".

For example, if stored procedure "A" is called by stored procedures "B" and
"C", I'm looking for any system table where the ID of "A" contains the ID's
of "B" and "C". I also checked conversely. Both are coming up empty.

Is there anywhere in the system tables that maintains relationships between
stored procedures? If so, where?

Thanks!

"Alejandro Mesa" wrote:

> Inquiring syscomments table.
>
> Example:
>
> use northwind
> go
>
> create procedure dbo.usp_Proc1
> as
> set nocount on
> return 0
> go
>
> create procedure dbo.usp_Proc2
> as
> set nocount on
> declare @rv int
>
> execute @rv = dbo.usp_Proc1
>
> return @@error
> go
>
> create procedure dbo.usp_Proc3
> as
> set nocount on
> declare @rv int
>
> execute @rv = dbo.usp_Proc1
>
> return @@error
> go
>
> declare @sp sysname
>
> set @sp = 'dbo.usp_Proc1'
>
> select
> object_name([id])
> from
> syscomments
> where
> objectproperty([id], 'IsProcedure') = 1
> and patindex('%exec% @rv = ' + @sp + '%', [text]) > 0
>
> -- or
>
> select
> object_name([id])
> from
> sysdepends
> where
> depid = object_id(@sp)
> go
>
> drop procedure dbo.usp_Proc2, dbo.usp_Proc3, dbo.usp_Proc1
> go
>
>
> -- Example 2, shows why not to use sysdepends table
> --
> -- When creating Proc2 and 3, You will get msgs saying that sql server
> cannot add
> -- rows to sysdepends for the current stored procedure because they depend
> -- on the missing object 'dbo.usp_Proc1'. The stored procedures will still
> be created.
>
> create procedure dbo.usp_Proc3
> as
> set nocount on
> declare @rv int
>
> execute @rv = dbo.usp_Proc1
>
> return @@error
> go
>
> create procedure dbo.usp_Proc2
> as
> set nocount on
> declare @rv int
>
> execute @rv = dbo.usp_Proc1
>
> return @@error
> go
>
> create procedure dbo.usp_Proc1
> as
> set nocount on
> return 0
> go
>
> declare @sp sysname
>
> set @sp = 'dbo.usp_Proc1'
>
> select
> object_name([id])
> from
> sysdepends
> where
> depid = object_id(@sp)
> go
>
> drop procedure dbo.usp_Proc2, dbo.usp_Proc3, dbo.usp_Proc1
> go
>
>
>
> AMB
>
>
> "Joe Palm" wrote:
>
> > Given a stored procedure written by us, how can we get a list of what other
> > stored procedures in the same database call it?
> > --
> > Joe Palm
> > Senior Technical Developer
> > Madison, WI

Relevant Pages

  • No columns defined in rowset
    ... I used ADO to called this stored procedure. ... @o_intRetVal int OUTPUT ... DECLARE @insAcct_error int ... -- Check for duplicate ACCOUNTID ...
    (microsoft.public.sqlserver.programming)
  • SQL Job stopping
    ... Each job executes one stored procedure. ... Following is the part of the stored procedure that's suppose to execute ... DECLARE @logdbname2 varchar ... DECLARE @fullcnt int, @trancnt int ...
    (microsoft.public.sqlserver.server)
  • Re: DTSSql Task and Stored Procedure
    ... Have a read of the "Using SET NOCOUNT ON" section. ... > @out_Param integer OUTPUT ... > declare @temp_var as int ...
    (microsoft.public.sqlserver.dts)
  • Re: Adding date to parameter
    ... I made the assumption that the stored procedure went by the @paramtername ... "Implicit conversion from data type datetime to int is not allowed. ... -- SET NOCOUNT ON added to prevent extra result sets from ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored Procedure calling another that returns a value, no output parameter
    ... This will return a recordset from GetID. ... > I have a Stored Procedure that I wrote a while ago that accepts 1 ... > DECLARE @NT_ID INT ...
    (microsoft.public.sqlserver.programming)