Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013

    Unanswered: List of Stored Procedures using a table


    I am using DB2 UDB version DB2 v9.7. I want to know is there any way to get the list of all stored procedures that are using a particular table i.e. just by knowing table name can I get the list of all related stored procedures that are using it.

    Would appreciate any help on this.

    Many thanks in advance.
    Last edited by Kapil006; 10-10-13 at 07:18. Reason: for clarification

  2. #2
    Join Date
    Apr 2012
    Provided Answers: 27
    Read the on-line infocenter description of SYSCAT.ROUTINEDEP in particular the BTYPE column, but keep in mind this will show only static references to the table.
    So for dynamic-sql you cannot use syscat.routinedep to see the dependencies (you might scan the procedure text however, though even that might not suffice).
    Also keep in mind that there are other dependencies that are indirect - for example views on the table etc, that you may need to consider, depending on your ultimate objective.

  3. #3
    Join Date
    Aug 2013
    Thanks for the information.

    Using your information, I have created below query to get list of stored procedures that are using a particular table.

    select a.procname from syscat.procedures a, syscat.routinedep b
    routineschema = <schema-name>
    and a.specificname = b.specificname
    AND B.BNAME = <table-name>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts