CODEWITHSUNDEEP
db2procedurerefer
Krishnam
Krishnam

Reputation: 849

DB2, Stored Procedures that are refering to a table name

In DB2 - Is there any SQL statement, when I execute, will return a list of Stored Procedures that are referring to a table name.

Upvotes: 0

Views: 2533

Answers (2)

data_henrik
data_henrik

Reputation: 17118

You can utilize the catalog view SYSCAT.ROUTINEDEP for that:

select specificname, routineschema from syscat.routinedep where btype='F' and bschema='yourschema' and bname='yourtable'

This will return all routines (incl. stored procedures) dependent on that referenced table identified by yourschema.yourtable

Upvotes: 1

mustaccio
mustaccio

Reputation: 18945

There is a catalog view SYSCAT.ROUTINEDEP:

SELECT R.ROUTINESCHEMA, R.ROUTINENAME 
FROM SYSCAT.ROUTINEDEP D, SYSCAT.ROUTINES R
WHERE D.ROUTINESCHEMA = R.ROUTINESCHEMA AND D.SPECIFICNAME = R.SPECIFICNAME 
AND D.BTYPE IN ('T','V','A') 
AND D.BSCHEMA = 'MYTABLESCHEMA' AND D.BNAME = 'MYTABLENAME'

Keep in mind that tables referenced by dynamic SQL will not appear in SYSCAT.ROUTINEDEP.

Upvotes: 1

Related Questions