Menu
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.
240
The easiest way to find all tables in SQL is to query the INFORMATION_SCHEMA views. You do this by specifying the information schema, then the “tables” view. Here's an example. SELECT table_name, table_schema, table_type FROM information_schema.
select tabname,tabschema from syscat. tables where tabname like 'PROJECT%' should show your tables and the schema name.
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.
189
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
