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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept