[sql] 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.

This should work:

select * from syscat.tables

Using the DB2 commands (no SQL) there is the possibility of executing

db2 LIST TABLES FOR ALL

This shows all the tables in all the schemas in the database.

ref: show all tables in DB2 using the LIST command

SELECT
  name
FROM
  SYSIBM.SYSTABLES
WHERE
    type = 'T'
  AND
    creator = 'MySchema'
  AND
    name LIKE 'book_%';

You can also get the table names simply by typing LIST TABLES in DB2

select name from sysibm.systables 
where name like '%ISP%' 
and type = 'T'

For Db2 for Linux, Unix and Windows (i.e. Db2 LUW) or for Db2 Warehouse use the SYSCAT.TABLES catalog view. E.g.

SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA LIKE '%CUR%' AND TYPE = 'T'

Which is a SQL statement that will return all standard tables in all schema that contains the substring CUR. From a Db2 command line you could also use a CLP command e.g. db2 list tables for all | grep CUR to similar effect

This page describes the columns in SYSCAT.TABLES including the different values for the TYPE column.

A = Alias
G = Created temporary table
H = Hierarchy table
L = Detached table
N = Nickname
S = Materialized query table
T = Table (untyped)
U = Typed table
V = View (untyped)
W = Typed view

Other commonly used catalog views incude

SYSCAT.COLUMNS      Lists the columns in each table, view and nickname
SYSCAT.VIEWS        Full SQL text for view and materialized query tables
SYSCAT.KEYCOLUSE    Column that are in PK, FK or Uniuqe constraints

In Db2 LUW it is considered bad practice to use the SYSIBM catalog tables (which the SYSCAT catalog views select thier data from). They are less consistent as far as column names go, are not quite as easy to use, are not documented and are more likely to change between versions.

This page has a list of all the catalog views Road map to the catalog views

For Db2 for z/OS, use SYSIBM.TABLES which is described here. E.g.

SELECT CREATOR, NAME FROM SYSIBM.SYSTABLES WHERE OWNER LIKE '%CUR%' AND TYPE = 'T'

For Db2 for i (i.e. iSeries aka AS/400) use QSYS2.SYSTABLES which is described here

SELECT TABLE_OWNER, TABLE_NAME FROM QSYS2.SYSTABLES WHERE TABLE_SCHEMA LIKE '%CUR%' AND TABLE_TYPE = 'T'

For DB2 Server for VSE and VM use SYSTEM.SYSCATALOG which is described here DB2 Server for VSE and VM SQL Reference

SELECT CREATOR, TNAME FROM SYSTEM.SYSCATALOG WHERE TABLETYPE = 'R'

DB2 LIST TABLES FOR SCHEMA <schema_name>

IN db2warehouse I found that "owner" doesn't exist, so I describe table syscat.systables and try using CREATOR instead and it works.

db2 "select NAME from sysibm.systables where CREATOR = '[SCHEMANAME]'and type = 'T'"

On my iSeries I have to run this command from iNavigator:

select *
from QSYS2.SYSTABLES
where TABLE_SCHEMA
like 'SCHEMA_NAME'
and TYPE = 'T';

You should try this:

select TABNAME from syscat.tables where tabschema = 'yourschemaname'";

db2 connect to MY_INSTACE_DB with myuser -- connect to db2    
db2 "select TABNAME from syscat.tables where tabschema = 'mySchema' with ur"
db2 terminate -- end connection

This is my working solution:

select tabname as table_name
from syscat.tables
where tabschema = 'schema_name' -- put schema name here
      and type = 'T'
order by tabname

There is no big difference in data.The Major difference is column order In list tables schema column will be after table/view column In list tables show details schema column will be after column type