Search code examples
sqldb2database-schema

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 
  • --for DB2/z
    select * from sysibm.systables
    where owner = 'SCHEMA'
    and name like '%CUR%'
    and type = 'T';

--for DB2/LUW
    select * from sysibm.systables
    where CREATOR = '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, as per the second query above.

    You should examine the IBM docs for your specific variant if you're using neither of those.