Db2 Query to Retrieve All Table Names for a Given Schema

DB2 Query to retrieve all table names for a given schema

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

show all tables in DB2 using the LIST command

To get a list of tables for the current database in DB2 -->

Connect to the database:

db2 connect to DATABASENAME user USER using PASSWORD

Run this query:

db2 LIST TABLES

This is the equivalent of SHOW TABLES in MySQL.

You may need to execute 'set schema myschema' to the correct schema before you run the list tables command. By default upon login your schema is the same as your username - which often won't contain any tables. You can use 'values current schema' to check what schema you're currently set to.

SQL query to get all the heading names under a specific schema name

Try using an additional filter

   WHERE TYPE = 'T'

this will limit your result to tables.

More details can be found here

Query to select all tables with specific last column Ibm Db2 z/os

SYSIBM.SYSTABLES

SYSIBM.SYSCOLUMNS

SELECT T.NAME AS TABLE_NAME
FROM SYSIBM.SYSTABLES T
JOIN SYSIBM.SYSCOLUMNS C ON C.TBNAME = T.NAME AND C.TBCREATOR = T.CREATOR
WHERE T.TYPE = 'T' AND T.CREATOR = 'WRIT5P1'
AND T.NAME NOT LIKE 'OLD%'
AND C.NAME = 'BATCH_ID'
AND C.COLNO = T.COLCOUNT

I don't remember if C.COLNO starts from 0 or 1. Edit the query accordingly, if it starts from 0.

DB2 view all tables and their information

Connect to the database:

db2 connect to <database-name>

List all tables:

db2 list tables for all

To list all tables in selected schema, use:

db2 list tables for schema <schema-name>

To describe a table, type:

db2 describe table <table-schema.table-name>

Search for a Column in all the schema

Try with the below Query.

select TBNAME
from sysibm.syscolumns
where NAME = '<column name>'

---

Related Topics