List materialized query tables in Db2 database

Query below lists all MQTs (materialized query tables) in IBM Db2 database.


select tab.tabschema as schema_name,
    tab.tabname as table_name,
from syscat.tables tab
where tab.type = 'S'
    and tab.tabschema not like 'SYS%'
order by tab.tabschema, tab.tabname


  • schema_name - schema name
  • table_name - table name
  • create_time - date the table was created
  • alter_time - date the table was last modified by using an ALTER statement


  • One row represents one MQT in the database
  • Scope of rows: all MQTs 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.