How to List Tables in IBM DB2

Data Tutorial

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.

Note: Since 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.
  • creator is the schema of the object.
  • type is the type of object and is represented by a single character. A value of T indicates the object is a table, V for view, A for alias, and so on.
  • dbname is 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 T:

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 creator column:

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 owner instead.

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 names:

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.