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

Marcin Nagly - Dataedo Team Marcin Nagly 2019-06-10

Table of Contents:


    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

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