How to find which procedures user a certain table

  • Hi, All:

    I have hundreds of procedures in my database. I wanted to add a field to a table.

    I am just wondering how to find which procedures use this table.

    I remember some time ago some one talks about this..but I can not seem to find it.

    Thanks and have great weekend…

    NG

  • From EM, right click on the table, select All Tasks, and select Display Dependencies.

    In QA or with T-SQL you may have to look into the sysdepends table (master database) for the info.


    Joseph

  • Try sp_depends

  • You could also do

    quote:


    SELECT sObj.name

    FROM dbo.sysobjects sObj

    INNER JOIN dbo.syscomments sComm

    ON sObj.id = sComm.id

    WHERE sComm.text LIKE '%tblServerlist%'

    AND sObj.type = 'P'


    This will list the stored procedure that have the text "tblServerList" in them.

    I've found many a stored procedure that wasn't referenced correctly in sysdepends, especially in an active development environment where things are dropped and re-created frequently.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

Viewing 4 posts - 1 through 3 (of 3 total)

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