Query below return all objects used by procedures in Db2 database.

Query

select r.routineschema as schema_name, 
       r.routinename as procedure_name,
       bschema as ref_object_schema, 
       bname as ref_object_name,
       case btype
            when 'A' then 'Table alias'
            when 'B' then 'Trigger'
            when 'F' then 'Routine'
            when 'G' then 'Global temporary table'
            when 'H' then 'Hierarchy table'
            when 'K' then 'Package'
            when 'L' then 'Detached table'
            when 'N' then 'Nickname'
            when 'O' then 'Privilege dependency'
            when 'Q' then 'Sequence'
            when 'R' then 'User-defined data type'
            when 'S' then 'Materialized query table'
            when 'T' then 'Table'
            when 'U' then 'Typed table'
            when 'V' then 'View'
            when 'W' then 'Typed View'
            when 'X' then 'Index extension'
            when 'Z' then 'XSR object'
            when 'q' then 'Sequence alias'
            when 'u' then 'Module alias'
            when 'v' then 'Global variable'
            when '*' then 'Anchored to the row of a base table'
            end as ref_object_type
from syscat.routinedep rd
join syscat.routines r
     on rd.routineschema = r.routineschema
     and rd.specificname = r.specificname
where r.routinetype = 'P'
      and r.routineschema not like 'SYS%'
      --and r.routineschema = 'schema_name' -- put schema name here
      --and r.routinename = 'procedure_name' -- put procedure name here
order by schema_name,
         procedure_name;

Columns

• schema_name - schema name
• procedure_name - provided procedure name
• ref_object_schema - schema name of the referenced object
• ref_object_name - name of the referenced object
• ref_object_type - type of referenced object

Rows

• One row represents one referenced object by provided procedure
• Scope of rows: all objects that are used by procedure in database
• Ordered by schema name and name of referenced object

Sample Result