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

User · Accepted Answer

select   from sysibm systables where owner    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  For that  I believe you want the CREATOR column  In any case  you should examine the IBM docs for your specific variant  The table name almost certainly won t change however  so just look up SYSIBM SYSTABLES for the details

User · Answer

You should try this   select TABNAME from syscat tables where tabschema    yourschemaname

User · Answer

This should work   select   from syscat tables

User · Answer

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

User · Answer

SELECT   name FROM   SYSIBM SYSTABLES WHERE     type    T    AND     creator    MySchema    AND     name LIKE  book

User · Answer

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

User · Answer

On my iSeries I have to run this command from iNavigator   select   from QSYS2 SYSTABLES where TABLE SCHEMA like  SCHEMA NAME  and TYPE    T

User · Answer

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

User · Answer

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

User · Answer

DB2 LIST TABLES FOR SCHEMA  lt schema name gt

User · Answer

select name from sysibm systables  where name like   ISP    and type    T

User · Answer

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

User · Answer

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

User · Answer

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

[sql] DB2 Query to retrieve all table names for a given schema

Examples related to sql

Examples related to db2

Examples related to database-schema