RE: What stored procedures call this stored procedure?

From: Alejandro Mesa (AlejandroMesa_at_discussions.microsoft.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 08:35:05 -0700

I know that my english is bad, that is the reason I included TWO examples.
The first one select the information from the system table SYSCOMMENTS (see
BOL) and the second one use system table SYSDEPENDS, but sysdepends is not
reliable because of the referred name resolution (see BOL). I do not know any
other place in sql 2000 where you can select information about stored
procedure denpendency.

AMB

"Joe Palm" wrote:

> 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

  • Re: Stored procedure error handling
    ... i'm trying to do some error checking on stored procedures and am ... > DECLARE @newSupplierId as bigint ... > INSERT INTO Supplier ... > SET NOCOUNT OFF ...
    (comp.databases.ms-sqlserver)
  • Re: Updating sql05 with Stored Procedure
    ... Have you provided stored procedures for select/insert/update/delete? ... @Original_CategoryID int, ... SET NOCOUNT OFF; ... SR.lutCategories WHERE (CategoryID = SCOPE_IDENTITY()) ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Stored procedure executes twice
    ... The SET NOCOUNT issue is an unrelated issue to yours (judging by your ... duplicate executions. ... >> Do your stored procedures contain SET NOCOUNT ON? ... >> used ADO); try changing the lock types to see if that helps. ...
    (microsoft.public.sqlserver.programming)
  • Re: Error msg
    ... I have corrected my stored procedures with what you said. ... INSERT Table1 (IID, Name) ... SET NOCOUNT OFF ... "Erland Sommarskog" wrote: ...
    (microsoft.public.data.oledb)
  • Re: Encrypted Stored Proc
    ... The problem with using syscomments is that some larger stored procedures ... > with encryption ... > declare @objname sysname ...
    (microsoft.public.sqlserver.programming)
Loading