DB2 Query to retrieve all table names for a given schema

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.

Solution 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.

Solution 2:

DB2 LIST TABLES FOR SCHEMA <schema_name>

Solution 3:

On my iSeries I have to run this command from iNavigator:

select *
from QSYS2.SYSTABLES
where TABLE_SCHEMA
like 'SCHEMA_NAME'
and TYPE = 'T';

Solution 4:

You should try this:

select TABNAME from syscat.tables where tabschema = 'yourschemaname'";