To query for a result containing all tables in the system, we need to use the
SYSIBM.SYSTABLES table, which is a catalog containing a record for every existing view, alias, or table.
SYSIBM.SYSTABLES contains a row for each of the three object types (view, alias, table), from now on throughout this little tutorial we’ll refer to each row as a potential
object, rather than specifying all three object types that are possible.
Like all normal tables in DB2,
SYSIBM.SYSTABLES has a number of columns that can be queried to retrieve all kinds of useful data:
name, of course, contains the name of the object.
creatoris the schema of the object.
typeis the type of object and is represented by a single character. A value of
Tindicates the object is a table,
Afor alias, and so on.
dbnameis the name of the database that contains the object, if applicable.
Many more columns exist and all the details can be found in the official documentation if you’d like to learn more.
Querying the SYSIBM.SYSTABLES Table
Now that we know where we need to look, we can actually execute some code and get our table list.
At the most basic level, we can simply perform a query to lookup all tables by ensuring the
type column is equal to
SELECT * FROM SYSIBM.SYSTABLES WHERE type = 'T';
Depending on the size of your database, this could return far too many records that you may not be interested in.
If desired, we can narrow down our search to a particular schema (or
owner) by filtering against the
SELECT * FROM SYSIBM.SYSTABLES WHERE type = 'T' AND creator = 'MySchema';
Note: Depending on your version of IBM DB2, you may need to compare the appropriate
schema against the
owner column rather than the
creator column. If the above doesn’t behave as expected, try
To filter results down even further, you may also wish to look for tables with a wildcard search of a few characters in the table
name. This can be particularly useful for a collection of tables that represent a particular system and use a
namespace prefix of some kind.
Additionally, we can of course filter the returned information a bit more too. In this final example, we’ll look for only tables with the prefix text
"book_" and ensure the only data we get are the actual table
SELECT name FROM SYSIBM.SYSTABLES WHERE type = 'T' AND creator = 'MySchema' AND name LIKE 'book_%';
That’s all there is to it; we can now easily query for table names across our entire IBM DB2 system.