List tables used by Materialized Query Tables (MQT dependencies) in Db2 database

Materialized query tables (MQTs) are a tables whose definition is based on the result of a query. The data for MQTs consists of one or more base tables on which MQTs definition is based. (You can think of an MQTs as a kind of materialized view.)

Query below lists all MQTs tables in SQL Server database with their dependency (e.g. base tables).

Query

select tabschema as schema_name,
tabname as table_name,
    case btype 
        when 'T' then 'Table'
        when 'A' then 'Table alias'
        when 'I' then 'Index'
        when 'V' then 'View'
        when 'F' then 'Routine'
        when 'N' then 'Nickname'
    end as dependency_type,
    bschema as dependency_schema_name,
    bname as dependency_table_name
from syscat.tabdep  
where dtype = 'S'
    and tabschema not like 'SYS%'
order by tabschema, tabname 

Columns

  • schema_name - schema name
  • table_name - table name
  • dependency_type - type of object on which there is a dependency:
    • Table
    • Table alias
    • Index
    • View
    • Routine
    • Nickname
  • dependency_schema_name - schema name of the object on which materialized query table depends
  • dependency_table_name - name of the object on which materialized query table depends.

Rows

  • One row represents one MQT table. If MQT table consists of multiple objects, each objects appears separately.
  • Scope of rows: all MQT tables in the database
  • Ordered by schema and name

Sample results