DB2 Query to retrieve all table names for a given schema

sql db2 database-schema
Ben picture Ben · Aug 27, 2010 · Viewed 237.6k times · Source

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

paxdiablo picture paxdiablo · Aug 27, 2010 
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.

Related questions

show all tables in DB2 using the LIST command

This is embarrassing, but I can't seem to find a way to list the names of the tables in our DB2 database. Here is what I tried: root@VO11555:~# su - db2inst1 root@VO11555:~# . ~db2inst1/sqllib/db2profile …

Read More
DB2 Date format

I just want to format current date into yyyymmdd in DB2. I see the date formats available, but how can I use them? http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.…

Read More
How to update multiple columns in single update statement in DB2

I want to update multiple columns of a table in DB2 with single Update statement. Any hint or idea will be appreciable. Thanks.

Read More