DB2 Query to retrieve all table names for a given schema

Question

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.

paxdiablo · Accepted Answer · 2022-07-15T00:01:13.340

86

--for DB2/z
    select * from sysibm.systables
    where owner = 'SCHEMA'
    and name like '%CUR%'
    and type = 'T';

--for DB2/LUW
    select * from sysibm.systables
    where CREATOR = '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, as per the second query above.

You should examine the IBM docs for your specific variant if you're using neither of those.

edited Jul 15 '22 at 00:01

answered Aug 27 '10 at 12:37

paxdiablo

854,327
234
1,573
1,953

3

`SQL0206N "OWNER" is not valid in the context where it is used. SQLSTATE=42703` – Marius Butuc Apr 11 '12 at 02:51
8

@Marius, you may have a different schema in your DB2 variant - this was specific to DB2/z, what we like to call the _real_ DB2 :-) For example, in DB2/LUW, you would use CREATOR. It doesn't change the underlying answer, which is to use `systables`. – paxdiablo Apr 11 '12 at 02:57
1

my bad: I am using **DB2/LUW** indeed. – Marius Butuc Apr 11 '12 at 03:09
1

Only IBM would port a product to a different OS and make it incompatible with the original... – Frans Jun 06 '18 at 11:23
The 4 platforms of DB2 (LUW, z/OS, i and VSE/VM) are not ports of one another, they have separate code bases and have been developed (moslty) independently. Db2 for i is not even a separate product, but an integral part of the iSeries operating system. Note that the Liunx (including zLinux), Unix and Windows versions are the same codebase and are the same product. – Paul Vernon Oct 19 '18 at 08:56
For those of you using IBM i, your query will be the following: ```sql SELECT * FROM qsys2.systables ``` – Anthony Rolfe Apr 15 '22 at 13:34

score 30 · Answer 2 · edited Feb 14 '17 at 10:09

30

DB2 LIST TABLES FOR SCHEMA <schema_name>

edited Feb 14 '17 at 10:09

Vitaliy Ulantikov

10,157
3
61
54

answered Sep 06 '11 at 09:09

deepthi

309
3
3

1

this option has the benefit of being case insensitive. – Brannon Mar 01 '13 at 16:53

Bert · Answer 3 · 2017-02-14T01:53:23.227

27

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

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

edited Feb 14 '17 at 01:53

answered Jan 05 '16 at 14:04

Bert

2,134
19
19

QSYS2.SYSTABLES was needed to list JD Edwards 9.2 tables on an IBM iSeries. – jla Aug 17 '18 at 19:46

score 18 · Answer 4 · edited Nov 30 '12 at 23:41

18

You should try this:

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

edited Nov 30 '12 at 23:41

Yannick Blondeau

9,465
8
52
74

answered Nov 30 '12 at 23:18

KBO

181
1
2

score 8 · Answer 5 · edited Jun 22 '17 at 11:27

8

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

edited Jun 22 '17 at 11:27

Mahbub

93
1
6

answered Aug 14 '14 at 08:35

schneiti

428
1
8
22

4

This is really handy if you don't know the schema that you're looking for, or want to dump a list of all the tables in your database to a file. `db2 "list tables for all" > tables.txt` – bonh Sep 15 '14 at 15:40
That's what I wanted! Thanks! – Rabia Naz khan Jul 04 '16 at 09:20

Paul Vernon · Answer 6 · 2022-10-13T09:55:41.167

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 include

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 Unique constraints

In Db2 LUW it is considered bad practice to use the SYSIBM catalog tables (which the SYSCAT catalog views select their 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

Also, for a general set of queries against the Db2 catalog (and system functions) you can look at the IBM samples library in the db-library section which includes views to do things like generate (approximate) DDL as well as many other things.

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'

Using uncommitted read (e.g by adding `WITH UR` to the end of your query) on catalog queries is generally a good idea to avoid waiting on other applications with uncommitted catalog changes. — Paul Vernon, Oct 02 '18 at 07:27
This answer was very helpful for my task of hunting down custom database items. Thank you for going into detail! — gritts, Jul 09 '20 at 01:06

score 4 · Answer 7 · answered Aug 08 '16 at 05:05

4

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

answered Aug 08 '16 at 05:05

Cristian

548
6
8

score 2 · Answer 8 · edited Apr 04 '12 at 10:33

2

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

edited Apr 04 '12 at 10:33

antyrat

27,479
9
75
76

answered Apr 04 '12 at 09:16

federico molteni

29
1

score 0 · Answer 9 · answered Dec 10 '13 at 06:16

0

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

answered Dec 10 '13 at 06:16

BharathRapolu

9
1

2

That's not true. `LIST TABLES` results in 0 tables for me. Whereas `LIST TABLES SCHEMAX`results in the 796 tables of that schema. Therefore, `LIST TABLES` does not necessarily show all the tables. – schneiti Aug 14 '14 at 08:33

score 0 · Answer 10 · answered Jan 17 '21 at 14:28

0

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

answered Jan 17 '21 at 14:28

dkero

121
1
8

score -1 · Answer 11 · edited Aug 20 '12 at 12:16

-1

This should work:

select * from syscat.tables

edited Aug 20 '12 at 12:16

Stewbob

16,759
9
63
107

answered Sep 07 '10 at 09:56

Fuangwith S.

5,654
8
37
41

score -1 · Answer 12 · edited Sep 27 '16 at 10:05

-1

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

edited Sep 27 '16 at 10:05

algrebe

1,621
11
17

answered Sep 27 '16 at 08:42

shaikshare

1
4

score -1 · Answer 13 · answered Mar 04 '17 at 14:43

-1

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

answered Mar 04 '17 at 14:43

mike

1

Xiao · Answer 14 · 2018-10-22T09:36:01.790

-1

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'"

edited Oct 22 '18 at 09:36

answered Oct 19 '18 at 04:45

Xiao

199
2
6

Use SYSCAT.TABLES in Db2 LUW and Db2 Warehouse. It has a TABSCHEMA column – Paul Vernon Oct 19 '18 at 06:34
I’ve updated my answer to include the schema/owner column for the 3 different Db2 platforms HTH – Paul Vernon Oct 19 '18 at 06:43

DB2 Query to retrieve all table names for a given schema

14 Answers14

Linked