Post reply

Is there a system procedure that lists what stored procedure(s) call a specific one?

  • January 11, 2010 at 10:15 am

    #137198

    Hi All,

    I ran "sp_depends" and is shows a list of what objects are dependant on a specific stored procedure. But I only want to know if there is a system procedure or some script that will gather the stored procedures that call a specific one. Does that make sense? I just want dependant stored procedures to a specific stored procedures. Please help!!!

    Thanks everybody!!! 😎

    -David

  • January 11, 2010 at 10:45 am

    #1101936

    This might help you out.

    declare @proc sysname

    set @proc = '<Your Procedure Name here>'

    SELECT schema_name(sp.schema_id) [Schema], object_name(sasm.object_id) [Procedure]

    FROM sys.all_sql_modules sasm

    INNER JOIN sys.procedures sp ON sasm.object_id = sp.object_id

    WHERE sasm.definition like '%' + @proc + '%'

    AND sasm.object_id <> object_id('dbo.' + @proc)

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 11, 2010 at 11:40 am

    #1101962

    Wayne,

    I tried the following, but got the error shown below. Any ideas what the prob is?

    declare @proc sysname

    set @proc = 'InsertWarehouseCountExisting'

    SELECT schema_name(sp.schema_id) [Schema], object_name(sasm.object_id) [Procedure]

    FROM sys.all_sql_modules sasm

    INNER JOIN sys.procedures sp ON sasm.object_id = sp.object_id

    WHERE sasm.definition like '%' + @proc + '%'

    AND sasm.object_id <> object_id('dbo.' + @proc)code]

    ERROR: Incorrect syntax near 'code'.

    Thanks,

    David

  • January 11, 2010 at 12:32 pm

    #1101994

    He left out an open-bracket on it. Should be:

    declare @proc sysname

    set @proc = 'InsertWarehouseCountExisting'

    SELECT schema_name(sp.schema_id) [Schema], object_name(sasm.object_id) [Procedure]

    FROM sys.all_sql_modules sasm

    INNER JOIN sys.procedures sp ON sasm.object_id = sp.object_id

    WHERE sasm.definition like '%' + @proc + '%'

    AND sasm.object_id <> object_id('dbo.' + @proc)

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 11, 2010 at 2:07 pm

    #1102049

    Thanks Gus.

    I saw that, and I'm sure that I fixed it before posting.... 😉

    Can I blame in on the way things get quoted 'round here?

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • January 11, 2010 at 6:58 pm

    #1102135

    Great script Wayne!



    My blog

  • January 12, 2010 at 7:05 am

    #1102393

    WayneS (1/11/2010)

    Thanks Gus.

    I saw that, and I'm sure that I fixed it before posting.... 😉

    Can I blame in on the way things get quoted 'round here?

    Makes sense to me.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply