sql - DB2 Query to retrieve all table names for a given schema

Question

I'm just looking for a simple query to select all the table names for a given schema.

For example, our DB has over 100 tables and I need to find any table that contains the sub-string “CUR”. I can use the like command once I have all the tables.

Answer 1

select * from sysibm.systables
where owner = 'SCHEMA'
and name like '%CUR%'
and type = 'T';

This will give you all the tables with CUR in them in the SCHEMA schema.

See here for more details on the SYSIBM.SYSTABLES table. If you have a look at the navigation pane on the left, you can get all sorts of wonderful DB2 metatdata.

Note that this link is for the mainframe DB2/z. DB2/LUW (the Linux/UNIX/Windows one) has slightly different columns. For that, I believe you want the CREATOR column.

In any case, you should examine the IBM docs for your specific variant. The table name almost certainly won't change however, so just look up SYSIBM.SYSTABLES for the details.