Post reply

List out all the procedures which uses a particualr table.

  • April 27, 2009 at 5:35 am

    #220601

    Dear All,

    I have modified the structure of a base table.

    Now I want to find out all the procedures using this particular table.

    (In some procedures I have written dynamic queries which uses the above table)

    Is there any way..? Pls help...

    Thanks in advance.

  • April 27, 2009 at 5:45 am

    #984270

    Hi San,

    Try to build script with sp_helptext 'procedure_name'. It resolves procedure's body to rowset.

    You can use cursor which will go through all procedures (which are in sysobjects table) and print only those which consist your table.

    Hope it helps,

    Cheers,

    Bartek

  • April 27, 2009 at 5:53 am

    #984275

    select object_name(object_id) from sys.sql_modules where definition like '%tablename%' should do it.

    You can modify the search condition any other way that suits your needs.

    "Keep Trying"

  • April 27, 2009 at 6:01 am

    #984281

    I have been spending too much time with SS2000, Chirag advice is definitely faster.

    In my solution you can use in SS2005 sys.objects table.

  • April 27, 2009 at 6:40 am

    #984305

    Thanks Chirag, this is wot exaclty I wanted.

    Cheers.

  • April 29, 2009 at 2:27 am

    #985688

    San (4/27/2009)

    Thanks Chirag, this is wot exaclty I wanted.

    Cheers.

    Welcome

    "Keep Trying"

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

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