show all tables in DB2 using the LIST command

Question

This is embarrassing  but I can t seem to find a way to list the names of the tables in our DB2 database  Here is what I tried   root VO11555    su - db2inst1 root VO11555       db2inst1 sqllib db2profile root VO11555    LIST ACTIVE DATABASES  We receive this error  SQL1092N   ROOT  does not have the authority to perform the requested command or operation   The DB2 version number follows   root VO11555    db2level DB21085I  Instance  db2inst1  uses  64  bits and DB2 code release  SQL09071  with level identifier  08020107   Informational tokens are  DB2 v9 7 0 1    s091114    IP23034   and Fix Pack  1   Product is installed at   opt db2V9 7

User · Accepted Answer

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.

User · Answer

select   from syscat tables where type    T    you may want to restrict the query to your tabschema

User · Answer

Connect to the database   db2 connect to  lt database-name gt    List all tables   db2 list tables for all   To list all tables in selected schema  use   db2 list tables for schema  lt schema-name gt    To describe a table  type   db2 describe table  lt table-schema table-name gt    credit  http   onewebsql com blog list-all-tables

User · Answer

I m using db2 7 1 and SQuirrel  This is the only query that worked for me    select   from SYSIBM tables where table schema    my schema  and table type    BASE TABLE

User · Answer

have you installed a user db2inst2  i think  i remember  that db2inst1 is very administrative

User · Answer

Run this command line on your preferred shell session   db2  select tabname from syscat tables where owner    DB2INST1     Maybe you d like to modify the owner name  and need to check the list of current owners   db2  select distinct owner from syscat tables

[sql] show all tables in DB2 using the LIST command

Examples related to sql

Examples related to db2