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