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 alias
- 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