Post reply

Finding where a function is being called in a stored procedure

  • July 9, 2008 at 8:25 am

    #72353

    Good morning all!

    I am in need of a script where I can query my database(s) and find where a function is being called. I have the function, now I just want to know what stored procedures are calling it.

    Thanks in advance, as always!! 😛

    Thank you!!,

    Angelindiego

  • July 9, 2008 at 8:42 am

    #840204

    Easy - I would generate a script of all my stored procedures and then search where the function name appears in it.

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • July 9, 2008 at 8:52 am

    #840216

    thank you!! That worked!!

    Thank you!!,

    Angelindiego

  • July 9, 2008 at 9:03 am

    #840235

    Hi,

    Instead of scripting all you procedures you could use this:

    SELECT

    o.Name as [Procedure Name],

    c.text as [Procedure Text]

    FROM sysComments c

    INNER JOIN sysObjects o

    ON o.Id = c.Id

    WHERE

    [o].xType = 'P'

    AND

    c.text LIKE '%MY FUNCTION%'

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 9, 2008 at 9:15 am

    #840261

    You rock! Thank you!!

    Thank you!!,

    Angelindiego

  • July 9, 2008 at 9:35 am

    #840283

    he he he

    no problem.

    I've extended it as follows:

    DECLARE @vcSearch VARCHAR(100)

    DECLARE @vcObjectName VARCHAR(100)

    DECLARE @vcObjectType VARCHAR(100)

    SELECT

    @vcSearch = ''

    ,@vcObjectName = ''

    ,@vcObjectType = ''

    SELECT DISTINCT

    so.Name,

    CASE WHEN so.xtype = 'p' THEN 'PROCEDURE'

    WHEN so.xtype = 'u' THEN 'TABLE'

    WHEN so.xtype = 'tr' THEN 'TRIGGER'

    WHEN so.xtype = 'fn' THEN 'FUNCTION'

    WHEN so.xtype = 'v' THEN 'VIEW'

    ELSE xtype

    END as [Object Type],

    sm.Definition

    FROM sysobjects so

    LEFT JOIN sys.sql_Modules sm ON so.id = sm.Object_id

    WHERE

    (sm.Definition LIKE '%' + @vcSearch + '%' OR @vcSearch='')

    AND

    (so.Name LIKE '%' + @vcObjectName + '%')

    AND

    (so.xType = @vcObjectType or @vcObjectType = '')

    ORDER BY so.Name

    If you leave the @vcSearch ='' then you get all (basically won't search the column)

    If you leave the @vcObjectType ='' then it will search all objects (tables procs etc)

    If you leave the @vcObjectName ='' then it won't care what the name of the object is.

    AS a result you can restrict but the text of the object (only applies to procs functions etc) as well as buy the object name and the type of object.

    enjoy

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 9, 2008 at 9:42 am

    #840298

    The only problem with that is if the function name sits on the "border" between two code segments in SysComments... you could miss a dependency that way. Works 99% of the time... the 1% miss may be a killer depending on what you want to do... just be aware... that's all. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2008 at 9:43 am

    #840299

    HI Jeff,

    Sorry you lost me on the column name there?

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 9, 2008 at 9:54 am

    #840312

    I am getting an error msg:

    Msg 208, Level 16, State 1, Line 11

    Invalid object name 'sys.sql_Modules'.

    do I need to run it against a certain DB?

    Thank you!!,

    Angelindiego

  • July 9, 2008 at 9:57 am

    #840314

    ooops now I see...

    that only works for 2005,

    for 2000 you need to use syscomments and thats when Jeffs beware comes into place

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 9, 2008 at 9:59 am

    #840319

    Christopher, can you explain a bit more in detail what the conversation you and Jeff are having is about?? I don't want to get lost here.....(ok, anymore lost.....)

    Thanks guys!!

    Thank you!!,

    Angelindiego

  • July 9, 2008 at 10:13 am

    #840330

    Christopher Stobbs (7/9/2008)

    HI Jeff,

    Sorry you lost me on the column name there?

    Sorry... I meant "SysComments"... not "SysColumns"... I've repaired the post where I made that error.

    The key is that the OP wants this done in SQL Server 2000... unlike SQL Server 2005 where you can get the whole proc in a single "swoop", in SQL Server 2000, you have to get the proc in 4k byte segments... if the function name happens to straddle to of those segments, you'll never find it.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2008 at 10:15 am

    #840332

    Angelindiego (7/9/2008)

    Christopher, can you explain a bit more in detail what the conversation you and Jeff are having is about?? I don't want to get lost here.....(ok, anymore lost.....)

    Thanks guys!!

    Sure, see my post immediately above...

    For SQL Server 2000, you need to look at the SysComments table to see the code for the various objects in SQL Server 2000...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 9, 2008 at 10:25 am

    #840341

    Thanks Jeff 🙂

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • July 9, 2008 at 10:37 am

    #840354

    ok, so now what db do I run this against so I don't get the error msg I spoke of????

    Thanks again guys!!

    Thank you!!,

    Angelindiego

Viewing 15 posts - 1 through 15 (of 16 total)

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