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).


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 


  • 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.


  • 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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.