List objects used by a view in Db2 database

Query below lists all objects used in views accessible to the current user in IBM DB2 database

Query

select tabschema as schema_name,
       tabname as view_name,
       bschema as referenced_schema,
       bname as referenced_name,
       btype as object_type
from syscat.tabdep
where dtype = 'V' 
      and tabschema not like 'SYS%'
order by schema_name,
         view_name;

Columns

  • schema_name - view's schema name
  • view_name - view name
  • referenced_schema - schema of the referenced object
  • referenced_name - name of the referenced object
  • referenced_type - type of the referenced object. Possible values:
    • A = Table alias
    • F = Routine
    • I = Index, if recording dependency on a base table
    • G = Global temporary table
    • N = Nickname
    • O = Privilege dependency on all subtables or subviews in a table or view hierarchy
    • R = User-defined structured type
    • S = Materialized query table
    • T = Table (untyped)
    • U = Typed table
    • V = View (untyped)
    • W = Typed view
    • Z = XSR object
    • m = Module
    • u = Module alias
    • v = Global variable

Rows

  • One row represents one object used in a specific view in a database
  • Scope of rows: all objects used by the views
  • Ordered by schema name, view name

Sample results

0
There are no comments. Click here to write the first comment.